# -*- coding: utf-8 -*-
from __future__ import unicode_literals
# from django.http import HttpResponse
from django.shortcuts import render
from django.shortcuts import redirect
from django.shortcuts import HttpResponse
from basic import models as basic_models
from django_celery_beat import models as beat_models
from cron import models as cron_models
from oratk import models as oratk_models
from django.db.models import Q, QuerySet
from django.core.paginator import Paginator
import cx_Oracle
import json
import time
from django.http import JsonResponse
import ast
import paramiko
import re
from datetime import datetime, date
import pymysql
import random
import os
from pathlib import Path

global curr_time
from dateutil.parser import parse
import openpyxl

global BASE_DIR
BASE_DIR = Path(__file__).resolve().parent.parent

# global curr_time2
curr_time = time.strftime('%Y年%m月%d日 %H:%M:%S', time.localtime(time.time()))
curr_time_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
# Create your views here.

# def login(request):
#     error_msg = ""
#     if request.method == 'POST':
#         user = request.POST.get('user',None)
#         pwd = request.POST.get('pwd',None)
#         login_obj = models.user_info.objects.filter(username=user,password=pwd).first()
#         if login_obj:
#             return redirect('/oratk/index.html')
#             #return HttpResponse("登入成功")
#         else:
#             error_msg = "用户名或密码错误"
#             #print request.path
#             return render(request, 'login.html', {'error_msg': error_msg})
#     if request.method == 'GET':
#         return render(request,'login.html',{'error_msg':error_msg})

"""def user_add(request):
    ##创建数据
    models.user_info.objects.create(username='test',password='test')
    ##查询 result 是对象列表 queryset类型
    ##查询所有
    #result = models.user_info.objects.all()
    ##带条件查询
    result = models.user_info.objects.filter(username='root')
    for row in result:
        print(row.username,row.password)
    ##删除
    #models.user_info.objects.filter(username='root').delete()
    ##更新
    models.user_info.objects.all().update(password='123')
"""


# def index(request):
#     if request.method == 'POST':
#         pass
#     if request.method == 'GET':
#         return render(request,'index.html')


def oratk_dashboard(request):
    if request.method == 'POST':
        pass
    if request.session.get('is_login', None):
        ##关闭浏览器删除session
        request.session.set_expiry(0)
        ins_active_info = []
        ins_inactive_info = []
        ins_locked_info = []
        ins_total_info = []
        ins_oracle_locked = basic_models.instance_info.objects.filter(status='LOCKED', type='ORACLE').count()
        ins_mysql_locked = basic_models.instance_info.objects.filter(status='LOCKED', type='MYSQL').count()
        ins_other_locked = basic_models.instance_info.objects.filter(status='LOCKED').exclude(type='MYSQL').exclude(
            type='ORACLE').count()

        ins_oracle_failed = basic_models.instance_info.objects.filter(ins_active='FAILED', type='ORACLE').count()
        ins_mysql_failed = basic_models.instance_info.objects.filter(ins_active='FAILED', type='MYSQL').count()
        ins_other_failed = basic_models.instance_info.objects.filter(ins_active='FAILED').exclude(type='MYSQL').exclude(
            type='ORACLE').count()

        ins_oracle_success = basic_models.instance_info.objects.filter(ins_active='SUCCESS', type='ORACLE').count()
        ins_mysql_success = basic_models.instance_info.objects.filter(ins_active='SUCCESS', type='MYSQL').count()
        ins_other_success = basic_models.instance_info.objects.filter(ins_active='SUCCESS').exclude(
            type='MYSQL').exclude(type='ORACLE').count()

        ins_total_oracle = basic_models.instance_info.objects.filter(type='ORACLE').count()
        ins_total_mysql = basic_models.instance_info.objects.filter(type='MYSQL').count()
        ins_total_other = basic_models.instance_info.objects.filter().exclude(type='MYSQL').exclude(
            type='ORACLE').count()

        ins_active_info.append(ins_oracle_success)
        ins_active_info.append(ins_mysql_success)
        ins_active_info.append(ins_other_success)
        ins_inactive_info.append(ins_oracle_failed)
        ins_inactive_info.append(ins_mysql_failed)
        ins_inactive_info.append(ins_other_failed)
        ins_locked_info.append(ins_oracle_locked)
        ins_locked_info.append(ins_mysql_locked)
        ins_locked_info.append(ins_other_locked)
        ins_total_info.append(ins_total_oracle)
        ins_total_info.append(ins_total_mysql)
        ins_total_info.append(ins_total_other)
        return render(request, 'oratk_app/oratk_dashboard.html',
                      {'ins_active_info': ins_active_info, 'ins_inactive_info': ins_inactive_info,
                       'ins_locked_info': ins_locked_info, 'ins_total_info': ins_total_info})
    else:
        return redirect('/basic_app/login.html')


def oratk_select(request):
    pass


### ora工具查询页 ####
def oratk_select_new(request):
    global select_name
    global select_conn_string
    global columns
    global result
    global dbname
    global conn_string
    if request.method == 'POST':
        ##获取要查询的数据库连接串
        # select_conn_string.split()[1] 为对应的连接串
        select_name = request.POST.get('tool_name', None)
        select_conn_string = request.POST.get('conn_string', None)
        submit_id = request.POST.get('submit_id', 0)
        arg_count = request.POST.get('arg_count', 0)
        dbname = select_conn_string.split('=')[0]
        conn_string = select_conn_string.split('=')[1]
        # print (select_name)
        # print (select_conn_string)
        ##获取查询项目对应的sql
        ##sql.sqltext 为对应的sql
        sql = basic_models.oratk_tool_info.objects.filter(name=select_name).first()
        ##数据库连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()
        sqltxt = sql.sqltext
        ##执行sql
        # cursor.execute(sql.sqltext)
        if arg_count == '1':
            print('参数个数为：1')
            arg1 = request.POST.get('arg1', None)
            print(arg1)
            # sqltxt = sqltxt.encode('utf-8').replace(':1',arg1)
            sqltxt = sqltxt.replace(':1', arg1)
            print(sqltxt)
        if arg_count == '2':
            print('参数个数为：2')
            arg1 = request.POST.get('arg1', None)
            arg2 = request.POST.get('arg2', None)
            print(arg1, arg2)
            # sqltxt = sqltxt.encode('utf-8').replace(':1',arg1).replace(':2',arg2)
            sqltxt = sqltxt.replace(':1', arg1).replace(':2', arg2)

            print(sqltxt)
        cursor.execute(sqltxt)
        ##列名
        columns = [col[0] for col in cursor.description]
        # print (columns)
        ##一条记录为一个字典,结果集为列表
        # cursor.rowfactory = lambda *args: dict(zip(columns, args))
        ##获取结果
        result = cursor.fetchall()
        print(result)
        if submit_id == 0:
            return render(request, 'oratk_app/oratk_select_use.html', {'select_name': select_name,
                                                                       'dbname': dbname,
                                                                       'select_conn_string': conn_string,
                                                                       'columns': columns,
                                                                       'result': result})
        else:
            return render(request, 'oratk_app/oratk_table_query.html', {'select_name': select_name,
                                                                        'dbname': dbname,
                                                                        'select_conn_string': conn_string,
                                                                        'columns': columns,
                                                                        'result': result})
    if request.method == 'GET':
        return render(request, 'oratk_app/oratk_select_use.html', {'select_name': select_name,
                                                                   'select_conn_string': select_conn_string,
                                                                   'columns': columns,
                                                                   'result': result})


def oratk_select_ora_bak(request):
    global select_name
    global select_conn_string
    global columns
    global result
    global dbname
    global conn_string
    global arg1
    global arg2
    global arg3
    if request.method == 'POST':
        ##获取查询项
        ##根据查询项获取sqltext
        select_name = request.POST.get('tool_name', None)
        sql = cron_models.ora_point_info.objects.filter(name=select_name).first()
        sqltxt = sql.sqltext
        ##获取根绝name=args获取参数列表
        argslist = request.POST.getlist('args', None)
        args_count = len(argslist)
        if args_count == '1':
            arg1 = argslist[0]
            sqltxt = sqltxt.replace(':1', arg1)
        if args_count == '2':
            arg1 = argslist[0]
            arg2 = argslist[1]
            sqltxt = sqltxt.replace(':1', arg1).replace(':2', arg2)
        if args_count == '3':
            arg1 = argslist[0]
            arg2 = argslist[1]
            arg3 = argslist[2]
            sqltxt = sqltxt.replace(':1', arg1).replace(':2', arg2).replace(':3', arg3)
        ##获取数据库连接串
        select_conn_string = request.POST.get('conn_string', None)
        ##获取数据库名字
        dbname = select_conn_string.split('=')[0]
        ##获取数据库连接串
        conn_string = select_conn_string.split('=')[1]
        ##db连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()
        ##获取sql执行方式
        exec_type = request.POST.get('exec_type', None)
        ##获取提交方式
        submit_id = request.POST.get('submit_id', 0)
        ##submit_id=0 为ajax提交方式的标记
        if submit_id == '0':
            if exec_type == 'sql执行':
                # 执行sql
                cursor.execute(sqltxt)
                ##列名
                columns = [col[0] for col in cursor.description]
                # print (columns)
                ##一条记录为一个字典,结果集为列表
                # cursor.rowfactory = lambda *args: dict(zip(columns, args))
                ##获取结果
                result = cursor.fetchall()
                result1 = cursor.fetchall()
                return render(request, 'oratk_app/oratk_table_ora.html', {'select_name': select_name,
                                                                          'dbname': dbname,
                                                                          'select_conn_string': conn_string,
                                                                          'columns': columns,
                                                                          'result': result})
            if exec_type == 'shell执行':
                pass
        ##form post提交方式
        else:
            if exec_type == 'sql执行':
                # 执行sql
                cursor.execute(sqltxt)
                ##列名
                columns = [col[0] for col in cursor.description]
                # print (columns)
                ##一条记录为一个字典,结果集为列表
                # cursor.rowfactory = lambda *args: dict(zip(columns, args))
                ##获取结果
                result = cursor.fetchall()
                return render(request, 'oratk_app/oratk_select_use.html', {'select_name': select_name,
                                                                           'dbname': dbname,
                                                                           'select_conn_string': conn_string,
                                                                           'columns': columns,
                                                                           'result': result})

            if exec_type == 'shell执行':
                pass
    if request.method == 'GET':
        return render(request, 'oratk_app/oratk_select_use.html', {'select_name': select_name,
                                                                   'select_conn_string': select_conn_string,
                                                                   'columns': columns,
                                                                   'result': result})


##ora工具查询页 ajax和form提交方式
def oratk_select_ora(request):
    global select_name
    global select_conn_string
    global columns
    global result
    global dbname
    global conn_string
    global arg1
    global arg2
    global arg3
    arg1 = ''
    arg2 = ''
    arg3 = ''
    if request.method == 'POST':

        # 定义request 的参数列表
        args_dic = {}

        ##获取数据库连接串
        select_conn_string = request.POST.get('conn_string', None)
        ##获取数据库名字
        dbname = select_conn_string.split('=')[0]
        try:
            ##获取数据库连接串
            conn_string = select_conn_string.split('=')[1]
            ##db连接
            db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
            cursor = db.cursor()

            ##获取提交方式
            submit_id = request.POST.get('submit_id', 1)
            if submit_id == '0':
                args_list = request.POST.get('args', 0)
                argslist = ast.literal_eval(args_list)
            else:
                ##获取根绝name=args获取参数列表
                argslist = request.POST.getlist('args', None)
            print(argslist)
            args_count = len(argslist)
            # print(argslist[0])
            if args_count == 1:
                arg1 = argslist[0]
                # sqltxt = sqltxt.replace(':1', arg1)
            if args_count == 2:
                arg1 = argslist[0]
                arg2 = argslist[1]
                # sqltxt = sqltxt.replace(':1', arg1).replace(':2', arg2)
            if args_count == 3:
                arg1 = argslist[0]
                arg2 = argslist[1]
                arg3 = argslist[2]
                # sqltxt = sqltxt.replace(':1', arg1).replace(':2', arg2).replace(':3', arg3)
            ##获取查询项
            ##根据查询项获取sqltext
            select_name = request.POST.get('tool_name', None)
            sql = cron_models.ora_point_info.objects.filter(name=select_name).first()
            sqltextlist = ast.literal_eval(sql.sqltext.replace('\r', '').replace('\n', ''))
            # print(sqltextlist)
            args_dic['select_name'] = select_name
            args_dic['dbname'] = dbname
            args_dic['select_conn_string'] = select_conn_string
            args_dic['conn_string'] = conn_string
            arc_c = 0
            for i in sqltextlist:
                args_dic['desc' + str(arc_c)] = i[0] + ':'
                # 替换sql
                sqltext = i[1].replace(':1', arg1).replace(':2', arg2).replace(':3', arg3)
                print(sqltext)
                cursor.execute(sqltext)
                ##列名
                args_dic['columns' + str(arc_c)] = [col[0] for col in cursor.description]
                ##一条记录为一个字典,结果集为列表
                # cursor.rowfactory = lambda *args: dict(zip(columns, args))
                ##获取结果
                # result = cursor.fetchall()
                args_dic['result' + str(arc_c)] = cursor.fetchall()

                arc_c = arc_c + 1
                # print(arc_c)

            ##获取sql执行方式
            exec_type = request.POST.get('exec_type', None)

            ##submit_id=0 为ajax提交方式的标记
            if submit_id == '0':
                if exec_type == 'sql执行':
                    # print(args_dic)
                    return render(request, 'oratk_app/oratk_table_ora.html', args_dic)
                if exec_type == 'shell执行':
                    pass
            ##form post提交方式
            else:
                if exec_type == 'sql执行':
                    return render(request, 'oratk_app/oratk_select_use_ora.html', args_dic)
                if exec_type == 'shell执行':
                    pass
        except Exception as err:
            # return HttpResponse("<span style='color:red'>ERR:  %s </splan>" % (err))
            return HttpResponse("AJAX_ERR:  %s " % (err))

    if request.method == 'GET':
        return render(request, 'oratk_app/oratk_select_use.html', {'select_name': select_name,
                                                                   'select_conn_string': select_conn_string,
                                                                   'columns': columns,
                                                                   'result': result})


### ora工具查询页  ####
def oratk_select_ora_args_ajax(request):
    if request.method == 'POST':
        ##获取ora_point name值
        point_name = request.POST.get('tool_name', None)
        point_info = cron_models.ora_point_info.objects.filter(name=point_name).first()
        # 是否有参数
        args_mk = point_info.args_mark
        # 执行方式
        exec_type = point_info.exec_type
        exec_type_list = ast.literal_eval(exec_type)
        if args_mk == '否':
            return render(request, 'oratk_app/oratk_ora_args_table_2.html', {'exec_type_list': exec_type_list})
        if args_mk == '是':
            ##参数值
            args_val = point_info.args_val
            args_val_list = args_val.split(';')
            return render(request, 'oratk_app/oratk_ora_args_table.html',
                          {'args_val_list': args_val_list, 'exec_type_list': exec_type_list})


##分页方法
def v_paginator(data, current_page_num, line_count_set):
    # 以下是分页
    # 5条一页
    # 总页数：paginator.num_pages
    # 当前页：current_page_num
    # 当前页数据：current_page
    # 总行数：paginator.count
    global paginator
    global current_page
    global page_range
    global paginator_num_pages_list
    # paginator = Paginator(data, 5)  # 对所有数据进行分页
    paginator = Paginator(data, line_count_set)
    global last_page
    last_page = paginator.num_pages

    paginator_num_pages_list = [x for x in range(1, paginator.num_pages + 1)]
    # if paginator_num_pages_list==[]:
    #     paginator_num_pages_list = [1]
    try:  # 捕捉前台传过来的数据，传过来不正常的数据都跳到第一页
        global current_page
        current_page = paginator.page(current_page_num)  # 拿哪一页
        if paginator.num_pages > 11:  # 判断总页数是否大于 10 页
            if current_page_num - 5 < 1:  # 页数小于前5页就显示前10页
                current_range = range(1, 11)
                page_range = current_range
                return current_range, current_page, paginator, page_range, last_page, paginator_num_pages_list
            elif current_page_num + 5 > paginator.num_pages:  # 页数大于最后5页就显示最后10页
                current_range = range(paginator.num_pages - 10, paginator.num_pages + 1)
                page_range = current_range
                return current_range, current_page, paginator, page_range, last_page, paginator_num_pages_list
            else:
                current_range = range(current_page_num - 4, current_page_num + 4)  # 其他范围为-5页到+5页
                page_range = current_range
                return current_range, current_page, paginator, page_range, last_page, paginator_num_pages_list
        else:
            page_range = paginator.page_range  # 小于10页就显示所有页数
            return page_range, current_page, paginator, page_range, last_page, paginator_num_pages_list

    except Exception as e:
        current_page_num = 1  # 随便乱传取第一页
        current_page = paginator.page(current_page_num)  # 随便乱传则取第一页
        current_range = range(1, 12)
        return paginator, current_page, current_range, page_range, last_page, paginator_num_pages_list


##分页方法
def v_paginator_2(data, current_page_num_2, line_count_set_2):
    # 以下是分页
    # 5条一页
    # 总页数：paginator.num_pages
    # 当前页：current_page_num
    # 当前页数据：current_page
    # 总行数：paginator.count
    global paginator_2
    global current_page_2
    global page_range_2
    global paginator_num_pages_list_2

    # paginator = Paginator(data, 5)  # 对所有数据进行分页
    paginator_2 = Paginator(data, line_count_set_2)
    global last_page_2
    last_page_2 = paginator_2.num_pages

    paginator_num_pages_list_2 = [x for x in range(1, paginator_2.num_pages + 1)]
    # if paginator_num_pages_list_2 == []:
    #     paginator_num_pages_list_2 = [1]

    try:  # 捕捉前台传过来的数据，传过来不正常的数据都跳到第一页
        global current_page_2
        current_page_2 = paginator_2.page(current_page_num_2)  # 拿哪一页
        if paginator_2.num_pages > 11:  # 判断总页数是否大于 10 页
            if current_page_num_2 - 5 < 1:  # 页数小于前5页就显示前10页
                current_range_2 = range(1, 11)
                page_range_2 = current_range_2
                return current_range_2, current_page_2, paginator_2, page_range_2, last_page_2, paginator_num_pages_list_2
            elif current_page_num_2 + 5 > paginator_2.num_pages:  # 页数大于最后5页就显示最后10页
                current_range_2 = range(paginator_2.num_pages - 10, paginator_2.num_pages + 1)
                page_range_2 = current_range_2
                return current_range_2, current_page_2, paginator_2, page_range_2, last_page_2, paginator_num_pages_list_2
            else:
                current_range_2 = range(current_page_num_2 - 4, current_page_num_2 + 4)  # 其他范围为-5页到+5页
                page_range_2 = current_range_2
                return current_range_2, current_page_2, paginator_2, page_range_2, last_page_2, paginator_num_pages_list_2
        else:
            page_range_2 = paginator_2.page_range  # 小于10页就显示所有页数
            return page_range_2, current_page_2, paginator_2, page_range_2, last_page_2, paginator_num_pages_list_2

    except Exception as e:
        current_page_num_2 = 1  # 随便乱传取第一页
        current_page_2 = paginator_2.page(current_page_num_2)  # 随便乱传则取第一页
        current_range_2 = range(1, 12)
        return paginator_2, current_page_2, current_range_2, page_range_2, last_page_2, paginator_num_pages_list_2


##功能使用初始页
def oratk_use(request):
    if request.method == 'POST':
        ##获取要查询的数据库连接串
        # select_conn_string.split()[1] 为对应的连接串
        select_name = request.POST.get('tool_name', None)
        select_conn_string = request.POST.get('conn_string', None)
        dbname = select_conn_string.split('=')[0]
        conn_string = select_conn_string.split('=')[1]
        # print dbname
        # print (select_conn_string)
        ##获取查询项目对应的sql
        ##sql.sqltext 为对应的sql
        sql = basic_models.oratk_tool_info.objects.filter(name=select_name).first()
        ##数据库连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()
        ##执行sql
        cursor.execute(sql.sqltext)
        ##列名
        columns = [col[0] for col in cursor.description]
        # print (columns)
        ##一条记录为一个字典,结果集为列表
        # cursor.rowfactory = lambda *args: dict(zip(columns, args))
        ##获取结果
        result = cursor.fetchall()
        print(result)
        # for res in row:
        #     print (res)
        #     for k,v in res.items():
        #         print (k)
        #         print (v)
        return render(request, 'oratk_app/oratk_select_use.html', {'select_name': select_name,
                                                                   'dbname': dbname,
                                                                   'select_conn_string': conn_string,
                                                                   'columns': columns,
                                                                   'result': result})
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
            oratk_tool_info = basic_models.oratk_tool_info.objects.all()
            return render(request, 'oratk_app/oratk_use.html',
                          {'instanceinfo_result': instanceinfo_result, 'oratk_tool_info': oratk_tool_info})


##ora功能使用
def oratk_use_ora(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
            instanceinfo_result_total = len(instanceinfo_result)
            oratk_tool_info = cron_models.ora_point_info.objects.all().order_by('name')
            oratk_tool_info_total = len(oratk_tool_info)
            print(type)
            return render(request, 'oratk_app/oratk_use_ora.html',
                          {'instanceinfo_result': instanceinfo_result, 'oratk_tool_info': oratk_tool_info,
                           'instanceinfo_result_total': instanceinfo_result_total,
                           'oratk_tool_info_total': oratk_tool_info_total})


##oratk 自定义查询
def oratk_query(request):
    if request.method == 'POST':
        ##获取要查询的数据库连接串
        # select_conn_string.split()[1] 为对应的连接串
        sqltext = request.POST.get('sqltext', None)
        select_conn_string = request.POST.get('conn_string', None)
        submit_id = request.POST.get('submit_id', 0)
        ##获取schema
        schema_name = request.POST.get('selectSchema', None)
        print(schema_name)
        current_schema_sql = "ALTER SESSION SET CURRENT_SCHEMA = %s" % (schema_name)

        print(current_schema_sql)
        dbname = select_conn_string.split('=')[0]
        conn_string = select_conn_string.split('=')[1]
        sql = sqltext
        # 如果有分号去掉分号
        if sql.rstrip()[-1] == ';':
            sql = sql.rstrip()[:-1]

        print(select_conn_string.split('=')[1])
        print(sql)
        ##数据库连接

        # select_conn_string.split()[1] 为对应的连接串
        try:
            db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
            cursor = db.cursor()
            ##执行sql
            cursor.execute(current_schema_sql)
            cursor.execute(sql)
            ##列名
            columns = [col[0] for col in cursor.description]
            # print (columns)
            ##一条记录为一个字典,结果集为列表
            # cursor.rowfactory = lambda *args: dict(zip(columns, args))

            ##获取结果
            result = cursor.fetchall()
            print(result)
            count_limt = 500

            if submit_id == 0:
                if len(result) >= count_limt:
                    return HttpResponse('超出显示限制,显示的条目数不超过500')
                else:
                    return render(request, 'oratk_app/oratk_select_query.html', {'dbname': dbname,
                                                                                 'select_conn_string': conn_string,
                                                                                 'columns': columns,
                                                                                 'sqltext': sql,
                                                                                 'result': result})
            else:
                if len(result) >= count_limt:
                    return HttpResponse('超出显示限制,显示的条目数不超过500')
                else:
                    return render(request, 'oratk_app/oratk_table_query.html', {'dbname': dbname,
                                                                                'select_conn_string': conn_string,
                                                                                'columns': columns,
                                                                                'result': result})

        except cx_Oracle.DatabaseError as msg:
            print('sql_exec_except:')
            error, = msg.args
            # 获取sql执行失败时的错误信息  #err cx_oracle 返回的报错
            err_msg = error.message
            respone = 'sql执行异常,报错信息为(sql_exec_except):  ' + err_msg
            return HttpResponse(respone)
        except Exception as err:
            return HttpResponse(err)

    if request.method == 'GET':
        currentpage_num = int(request.GET.get('page', '1'))
        line_count_set = int(request.GET.get('line_count', 5))
        user_search = request.GET.get('user_search', 'all')

        url = '/oratk_app/oratk_query_savesql_delete_ajax'
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
            oratk_tool_info_result = cron_models.oratk_query_savesql.objects.all().order_by('-crtime')
            instanceinfo_result_total = len(instanceinfo_result)
            v_paginator(oratk_tool_info_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_query.html',
                          {'url': url, 'current_page': current_page, 'paginator': paginator,
                           'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search, 'instanceinfo_result': instanceinfo_result,
                           'instanceinfo_result_total': instanceinfo_result_total})


###oratk 自定义查询 请求schema ajax
def oratk_query_selectSchema_ajax(request):
    if request.method == 'POST':
        ##获取要查询的数据库连接串
        # select_conn_string.split()[1] 为对应的连接串
        select_conn_string = request.POST.get('conn_string', None)
        print(select_conn_string)
        dbname = select_conn_string.split('=')[0]
        conn_string = select_conn_string.split('=')[1]
        try:
            sql = cron_models.ora_point_info.objects.filter(name='username').values('sqltext').first()
            sql = ast.literal_eval(sql['sqltext'])[0][1]
            # ast.literal_eval
            # 如果有分号去掉分号
            if sql.rstrip()[-1] == ';':
                sql = sql.rstrip()[:-1]

            print(select_conn_string.split('=')[1])
            print(sql)
            ##数据库连接

            # select_conn_string.split()[1] 为对应的连接串
            try:
                db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
                cursor = db.cursor()
                ##执行sql
                cursor.execute(sql)
                ##列名
                columns = [col[0] for col in cursor.description]
                # print (columns)
                ##一条记录为一个字典,结果集为列表
                # cursor.rowfactory = lambda *args: dict(zip(columns, args))

                ##获取结果
                result = cursor.fetchall()
                print(result)
                return render(request, 'oratk_app/oratk_query_selectSchema_ajax.html', {'result': result})

            except cx_Oracle.DatabaseError as msg:
                print('sql_exec_except:')
                error, = msg.args
                # 获取sql执行失败时的错误信息  #err cx_oracle 返回的报错
                err_msg = error.message
                respone = 'sql执行异常,报错信息为(sql_exec_except):  ' + err_msg
                return HttpResponse(respone)
            except Exception as err:
                print(err)
                return HttpResponse(err)
        except Exception as err:
            print(err)
            return HttpResponse(err)

    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
            return render(request, 'oratk_app/oratk_query.html', {'instanceinfo_result': instanceinfo_result})


###oratk 自定义查询 保存sql  ajax
def oratk_query_savesql_delete_ajax(request):
    if request.method == 'POST':
        currentpage_num = int(request.POST.get('page', '1'))
        line_count_set = int(request.POST.get('line_count', 5))
        user_search = request.POST.get('user_search', 'all')

        url = '/oratk_app/oratk_query_savesql_delete_ajax'

        ##删除信息
        delete_tag = request.POST.get('delete_tag', None)
        ##保存信息
        save_sql_tag = request.POST.get('save_sql_tag', None)
        try:
            if delete_tag == 'YES':
                nid = request.POST.get('nid', None)
                ##删除数据
                cron_models.oratk_query_savesql.objects.filter(id=nid).delete()
            elif save_sql_tag == 'YES':
                save_sql_name = request.POST.get('save_sql_name', None)
                sqltext = request.POST.get('sqltext', None)
                ##保存到表
                cron_models.oratk_query_savesql.objects.create(name=save_sql_name, sqltext=sqltext)
            if user_search != 'all':
                oratk_tool_info_result = cron_models.oratk_query_savesql.objects.filter(Q(name__contains=user_search)
                                                                                        | Q(
                    sqltext__contains=user_search)
                                                                                        | Q(
                    remark__contains=user_search)).order_by('-crtime')
                v_paginator(oratk_tool_info_result, currentpage_num, line_count_set)
            else:
                user_search = ''
                oratk_tool_info_result = cron_models.oratk_query_savesql.objects.all().order_by('-crtime')
                v_paginator(oratk_tool_info_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_table_show_sql_ajax.html',
                          {'url': url, 'current_page': current_page, 'paginator': paginator,
                           'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list, 'search': user_search})
        except Exception as err:
            print(err)
            return HttpResponse(err)

    if request.method == 'GET':
        pass


def oratk_use_ajax(request):
    if request.method == 'POST':
        res = {'col': '', 'data': ''}
        select_conn_string = request.POST.get('v_dbname', None)
        select_name = request.POST.get('v_name', None)
        # 获取查询项目对应的sql
        ##sql.sqltext 为对应的sql
        sql = basic_models.oratk_tool_info.objects.filter(name=select_name).first()
        ##数据库连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()
        ##执行sql
        cursor.execute(sql.sqltext)
        ##列名
        columns = [col[0] for col in cursor.description]
        # print (columns)
        ##一条记录为一个字典,结果集为列表
        # cursor.rowfactory = lambda *args: dict(zip(columns, args))
        ##获取结果
        result = cursor.fetchall()
        res['col'] = columns
        res['data'] = result
        print(res)
        print(json.dumps(res))
        return HttpResponse(json.dumps(res))


### 功能点增加页 ###
def oratk_tool_info(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            ##当用户搜索时：
            if user_search != 'all':
                oratk_tool_info_result = basic_models.oratk_tool_info.objects.filter(Q(name__contains=user_search)
                                                                                     | Q(sqltext__contains=user_search)
                                                                                     | Q(status__contains=user_search)
                                                                                     | Q(remake__contains=user_search))
                v_paginator(oratk_tool_info_result, currentpage_num, line_count_set)
            # 当用户没有搜索时：
            else:
                user_search = ''
                oratk_tool_info_result = basic_models.oratk_tool_info.objects.all()
                v_paginator(oratk_tool_info_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_tool_info.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')


##ora工具功能项管理
def oratk_tool_info_ora(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            ##当用户搜索时：
            if user_search != 'all':
                oratk_tool_info_result = cron_models.ora_point_info.objects.filter(Q(name__contains=user_search)
                                                                                   | Q(sqltext__contains=user_search)
                                                                                   | Q(status__contains=user_search)
                                                                                   | Q(remark__contains=user_search))
                v_paginator(oratk_tool_info_result, currentpage_num, line_count_set)
            # 当用户没有搜索时：
            else:
                user_search = ''
                oratk_tool_info_result = cron_models.ora_point_info.objects.all()
                v_paginator(oratk_tool_info_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_tool_info_ora.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')


### 添加功能点 ###
def oratk_tool_add(request):
    if request.method == 'POST':
        add_name = request.POST.get('add_name', None)
        add_sqltext = request.POST.get('add_sqltext', None)
        add_status = request.POST.get('add_status', None)
        add_remake = request.POST.get('add_remake', None)
        basic_models.oratk_tool_info.objects.create(name=add_name, sqltext=add_sqltext, status=add_status,
                                                    remake=add_remake)
        return redirect("/oratk_app/oratk_tool_info")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/oratk_tool_add.html')
        else:
            return redirect('/basic_app/login.html')


## 编辑功能点
def user_edit(request):
    if request.method == 'POST':
        v_id = request.POST.get('v_id', None)
        add_name = request.POST.get('add_name', None)
        add_sqltext = request.POST.get('add_sqltext', None)
        add_status = request.POST.get('add_status', None)
        add_remake = request.POST.get('add_remake', None)
        basic_models.oratk_tool_info.objects.filter(id=v_id).update(name=add_name, sqltext=add_sqltext,
                                                                    status=add_status, remake=add_remake)
        return redirect("/oratk_app/oratk_tool_info")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            edit_result = basic_models.oratk_tool_info.objects.get(id=nid)
            return render(request, 'oratk_app/oratk_tool_edit.html', {'oratk_tool_info_result': edit_result})
        else:
            return redirect('/basic_app/login.html')


##功能删除
def oratk_tool_delete(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            basic_models.oratk_tool_info.objects.filter(id=nid).delete()
            return redirect("/oratk_app/oratk_tool_info")
        else:
            return redirect('/basic_app/login.html')


##功能锁定
def oratk_tool_lock(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            basic_models.oratk_tool_info.objects.filter(id=nid).update(status='locked')
            return redirect("/oratk_app/oratk_tool_info")
        else:
            return redirect('/basic_app/login.html')


##功能详情页
def oratk_tool_more(request):
    if request.method == 'POST':
        return redirect("/oratk_app/oratk_tool_info")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            more_result = basic_models.oratk_tool_info.objects.get(id=nid)
            return render(request, 'oratk_app/oratk_tool_more.html', {'more_result': more_result})
        else:
            return redirect('/basic_app/login.html')



### 添加或编辑功能点 ###
def oratk_tool_info_ora_add_point_ajax(request):
    if request.method == 'POST':
        try:
            v_id = request.POST.get('id', None)
            add_name = request.POST.get('add_name', None)
            add_sqltext = request.POST.get('add_sqltext', None)
            add_desc = request.POST.get('add_desc', None)
            add_args_mark = request.POST.get('add_args_mark', None)
            add_args_count = request.POST.get('add_args_count', None)
            add_args_val = request.POST.get('add_args_val', None)
            add_exec_type = request.POST.getlist('add_exec_type', None)
            add_status = request.POST.get('add_status', None)
            add_remark = request.POST.get('add_remark', None)
            if v_id == '':
                cron_models.ora_point_info.objects.create(name=add_name, sqltext=add_sqltext, desc=add_desc,
                                                          args_mark=add_args_mark, args_count=add_args_count,
                                                          args_val=add_args_val, exec_type=add_exec_type,
                                                          status=add_status,
                                                          remark=add_remark)

            else:
                cron_models.ora_point_info.objects.filter(id=v_id).update(name=add_name, sqltext=add_sqltext,
                                                                          desc=add_desc,
                                                                          args_mark=add_args_mark,
                                                                          args_count=add_args_count,
                                                                          args_val=add_args_val,
                                                                          exec_type=add_exec_type,
                                                                          status=add_status,
                                                                          remark=add_remark)
            return HttpResponse('SUCCESS')
        except Exception as e:
            return HttpResponse(e)


##删除或锁定
def oratk_tool_info_ora_delete_lock_ajax(request):
    if request.method == 'POST':
        try:
            v_id = request.POST.get('id', None)
            action_mark = request.POST.get('action_mark', None)
            if action_mark == 'delete':
                cron_models.ora_point_info.objects.filter(id=v_id).delete()
            if action_mark == 'lock':
                cron_models.ora_point_info.objects.filter(id=v_id).update(status='locked')
            return HttpResponse('SUCCESS')
        except Exception as e:
            return HttpResponse(e)
##获取sqltext
def oratk_tool_info_ora_getSqltext_ajax(request):
    if request.method == 'POST':
        nid = request.POST.get('nid')
        result = cron_models.ora_point_info.objects.filter(id=nid).values('sqltext').first()
        return HttpResponse(result['sqltext'])
##获取功能点信息
def oratk_tool_info_ora_point_info_ajax(request):
    if request.method == 'POST':
        nid = request.POST.get('nid')
        result = cron_models.ora_point_info.objects.filter(id=nid).values('id','name','desc','sqltext','args_mark','args_count','args_val','exec_type','remark','status').first()
        return HttpResponse(json.dumps(result),content_type='application/json')
##修改sqltext
def oratk_tool_info_ora_SqltextModify_ajax(request):
    if request.method == 'POST':
        try:
            nid = request.POST.get('nid')
            sqltext_content = request.POST.get('sqltext_content')
            print(nid)
            print(sqltext_content)
            ##filter方法不会自动更新修改时间
            #cron_models.ora_point_info.objects.filter(id=nid).update(sqltext=sqltext_content)
            ##save方法可以自动更新修改时间
            obj = cron_models.ora_point_info.objects.get(id=nid)
            obj.sqltext = sqltext_content
            obj.save()
            return HttpResponse('SUCCESS')
        except Exception as err:
            err = 'ERROR:'+err
            return HttpResponse(err)
###ora工具shell
## 获取scripts中 args
def get_script_args():
    print('get_script_args')
    server_ip = '12.18.0.16'
    server_port = 22
    server_username = 'oracle'
    server_pwd = 'oracle'
    server_shell_file = '/home/oracle/dbmonitor/ora'
    try:
        ##ssh连接
        client = paramiko.SSHClient()
        client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        ##连接服务器
        client.connect(hostname=server_ip, port=server_port, username=server_username, password=server_pwd)
        ##拼装命令
        command = 'cat ' + server_shell_file
        # print(command)
        stdin, stdout, stderr = client.exec_command(str(command))
        res_suc = str(stdout.read(), 'utf-8')
        res_err = str(stderr.read(), 'utf-8')
        ##从服务器把脚本内容写入本地
        with open('/Users/liwk/py/oratk_web/templates/shell_content/ora_args_tmp.txt', 'w', encoding='utf-8') as f:
            f.write(res_suc)
            f.close()
        ##分析脚本内容
        with open('/Users/liwk/py/oratk_web/templates/shell_content/ora_args_tmp.txt', 'r', encoding='utf-8') as f:
            # print (f.readlines())
            pattern = re.compile(r'\s*if \[\s*"\$1"\s*=')
            args_list = []
            for line in f.readlines():
                if pattern.search(line):
                    match = re.findall(r'"([^"]*)"', line)
                    # print(match,type(match))
                    args_list = args_list + match
            # print(args_list)
            ##list去重
            args_list = sorted(list(set(args_list)))
            args_list.remove('$1')
            cron_models.oratk_script_args.objects.create(args_values=args_list)
            f.close()
            return 'success'
    except Exception as e:
        return e


def oratk_select_ora_shell_args_refresh(request):
    if request.method == 'POST':
        try:
            get_et_script_info = get_script_args()
            if get_et_script_info == 'success':
                print('oratk_select_ora_shell_args_refresh')
                script_args_result = cron_models.oratk_script_args.objects.all().order_by('-crtime').first()
                script_args_result = ast.literal_eval(script_args_result.args_values)
                script_args_result_total = len(script_args_result)
                return render(request, 'oratk_app/oratk_use_ora_shell_refresh_args.html',
                              {'script_args_result': script_args_result,
                               'script_args_result_total': script_args_result_total})
            else:
                err_info = 'ERR:' + get_et_script_info
                return HttpResponse(err_info)
        except Exception as e:
            err_info = 'ERR:' + e
            return HttpResponse(err_info)


##获取脚本命令说明
def oratk_select_ora_shell_args_explain(request):
    if request.method == 'POST':
        try:
            args_value = request.POST.get('args', None)
            with open('/Users/liwk/py/oratk_web/templates/shell_content/ora_args_tmp.txt', 'r', encoding='utf-8') as f:
                file_content_list = f.readlines()
                i = 1
                pattern_command_usage_flag = re.compile(r'^\s*-\s*{}'.format(args_value))
                pattern_multi_line = re.compile(r'^\s*\w+')
                end_command_usage_flag = '##end command usage'
                pattern_end_command_usage_flag = re.compile(r'^{}*'.format(end_command_usage_flag))
                for line in file_content_list:
                    if pattern_end_command_usage_flag.search(line):
                        break
                    if pattern_command_usage_flag.search(line):
                        ii = 1
                        for line_more in file_content_list[i:]:
                            if pattern_end_command_usage_flag.search(line):
                                break
                            if pattern_multi_line.search(line_more):
                                line = line + line_more
                                ii = ii + 1
                            else:
                                break
                        line = 'command usage: ' + line
                        return render(request, 'oratk_app/oratk_use_ora_shell_args_explain.html',
                                      {'line': line, 'row_num': ii})
                    i = i + 1
                line = 'command_usage: no found command explain, palease see shell_script.'
                return render(request, 'oratk_app/oratk_use_ora_shell_args_explain.html', {'line': line, 'row_num': 1})
        except Exception as e:
            err_info = 'ERR:' + e
            return HttpResponse(err_info)


##ora工具shell
def oratk_use_ora_shell(request):
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
            script_args_result = cron_models.oratk_script_args.objects.all().order_by('-crtime').first()
            # 将字符串转换成list
            script_args_result = ast.literal_eval(script_args_result.args_values)
            script_args_result_total = len(script_args_result)
            instanceinfo_result_total = len(instanceinfo_result)
            ##将字符串转换成list,并且排序
            # script_args_result = sorted(ast.literal_eval(script_args_result.args_values))
            # print(script_args_result.remove('$1'))
            return render(request, 'oratk_app/oratk_use_ora_shell.html',
                          {'instanceinfo_result': instanceinfo_result, 'script_args_result': script_args_result,
                           'script_args_result_total': script_args_result_total,
                           'instanceinfo_result_total': instanceinfo_result_total})


##ora工具脚本管理
def oratk_tool_info_ora_shell(request):
    if request.method == 'GET':
        server_ip = '12.18.0.16'
        server_port = 22
        server_username = 'oracle'
        server_pwd = 'oracle'
        server_shell_file = '/home/oracle/dbmonitor/ora'
        history_version_table = 'cron_ora_shell_content_version'

        currentpage_num = int(request.GET.get('page', '1'))
        line_count_set = int(request.GET.get('line_count', 5))
        user_search = request.GET.get('user_search', 'all')
        url = 'oratk_tool_info_ora_shell_paginator_ajax'

        try:
            ##获取ora脚本历史版本
            oratk_tool_orashell_info_result = cron_models.ora_shell_content_version.objects.all().order_by('-crtime')
            v_paginator(oratk_tool_orashell_info_result, currentpage_num, line_count_set)
            ##ssh连接
            client = paramiko.SSHClient()
            client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            ##连接服务器
            client.connect(hostname=server_ip, port=server_port, username=server_username, password=server_pwd)
            ##拼装命令
            command = 'cat ' + server_shell_file
            # print(command)
            stdin, stdout, stderr = client.exec_command(str(command))
            res_suc = str(stdout.read(), 'utf-8')
            res_err = str(stderr.read(), 'utf-8')
            if request.session.get('is_login', None):
                ##关闭浏览器删除session
                request.session.set_expiry(0)
                if len(res_suc) > 0:
                    return render(request, 'oratk_app/oratk_tool_info_ora_shell.html',
                                  {'res_suc': res_suc, 'server_ip': server_ip, 'server_shell_file': server_shell_file,
                                   'history_version_table': history_version_table, 'url': url,
                                   'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                                   'page_range': page_range, 'last_page': last_page,
                                   'current_page_num': currentpage_num,
                                   'paginator_num_pages_list': paginator_num_pages_list, 'search': user_search})
                else:
                    return render(request, 'oratk_app/oratk_tool_info_ora_shell.html',
                                  {'res_err': res_err, 'server_ip': server_ip, 'server_shell_file': server_shell_file,
                                   'history_version_table': history_version_table, 'url': url,
                                   'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                                   'page_range': page_range, 'last_page': last_page,
                                   'current_page_num': currentpage_num,
                                   'paginator_num_pages_list': paginator_num_pages_list, 'search': user_search})
            else:
                return redirect('/basic_app/login.html')
        except Exception as e:
            res_err = e
            if request.session.get('is_login', None):
                ##关闭浏览器删除session
                request.session.set_expiry(0)
                return HttpResponse(e)
            else:
                return redirect('/basic_app/login.html')


def oratk_tool_info_ora_shell_paginator_ajax(request):
    if request.method == 'POST':
        currentpage_num = int(request.POST.get('page', '1'))
        line_count_set = int(request.POST.get('line_count', 5))
        user_search = request.POST.get('user_search', 'all')
        url = 'oratk_tool_info_ora_shell_paginator_ajax'
        select_byid_tag = request.POST.get('script_content_byid', None)
        nid = request.POST.get('nid', None)

        try:
            ##返回某个版本的脚本内容
            if select_byid_tag == 'YES':
                oratk_tool_orashell_info_result = cron_models.ora_shell_content_version.objects.filter(id=nid).first()
                return HttpResponse(oratk_tool_orashell_info_result.shell_content)
            else:
                ##返回搜索和分页
                if user_search != 'all':
                    oratk_tool_orashell_info_result = cron_models.ora_shell_content_version.objects.filter(
                        Q(change_show__contains=user_search)).order_by('-crtime')
                    v_paginator(oratk_tool_orashell_info_result, currentpage_num, line_count_set)
                else:
                    oratk_tool_orashell_info_result = cron_models.ora_shell_content_version.objects.all().order_by(
                        '-crtime')
                    v_paginator(oratk_tool_orashell_info_result, currentpage_num, line_count_set)
                return render(request, 'oratk_app/oratk_table_tool_info_shell_ajax.html',
                              {'url': url,
                               'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                               'page_range': page_range, 'last_page': last_page,
                               'current_page_num': currentpage_num,
                               'paginator_num_pages_list': paginator_num_pages_list, 'search': user_search})
        except Exception as e:
            res_err = e
            return HttpResponse(res_err)


def oratk_tool_info_ora_shell_edit_ajax(request):
    if request.method == 'POST':
        server_ip = '12.18.0.16'
        server_port = 22
        server_username = 'oracle'
        server_pwd = 'oracle'
        server_shell_file = '/home/oracle/dbmonitor/ora'
        try:
            ##ssh连接
            client = paramiko.SSHClient()
            client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            ##连接服务器
            client.connect(hostname=server_ip, port=server_port, username=server_username, password=server_pwd)
            ##拼装命令
            command = 'cat ' + server_shell_file
            # print(command)
            stdin, stdout, stderr = client.exec_command(str(command))
            res_suc = str(stdout.read(), 'utf-8')
            res_err = str(stderr.read(), 'utf-8')
            if len(res_suc) > 0:
                return HttpResponse(res_suc)
            else:
                return HttpResponse(res_err)
        except Exception as e:
            return HttpResponse(e)


##ora工具脚本管理
def oratk_tool_info_ora_shell_edit(request):
    server_ip = '12.18.0.16'
    server_port = 22
    server_username = 'oracle'
    server_pwd = 'oracle'
    server_shell_file = '/home/oracle/dbmonitor/ora'
    if request.method == 'POST':

        shell_content = request.POST.get('shell_content', None)
        change_desc = request.POST.get('change_desc', None)
        # 将修改后ora文件内容保存到数据库中
        cron_models.ora_shell_content_version.objects.create(server_ip=server_ip, server_port=server_port,
                                                             shell_path=server_shell_file,
                                                             shell_content=shell_content,
                                                             change_show=change_desc)

        ##将修改后的ora文件内容本地文件中
        with open('/Users/liwk/py/oratk_web/templates/shell_content/ora', 'w', encoding='utf-8') as f:
            f.write(shell_content.replace('\r\n', '\n'))
        try:
            ##sftp使用
            client = paramiko.Transport(server_ip, server_port)
            client.connect(username=server_username, password=server_pwd)
            sftp = paramiko.SFTPClient.from_transport(client)
            sftp.put("/Users/liwk/py/oratk_web/templates/shell_content/ora", server_shell_file)
            client.close()
            ##重新获取脚本中args_list
            get_script_args()
            return HttpResponse('success')
        except Exception as e:
            res_err = e
            return HttpResponse("修改脚本内容出错,%s" % (res_err))
    if request.method == 'GET':
        pass


###ora工具shell执行  ajax和form访问
def oratk_select_ora_shell(request):
    if request.method == 'POST':
        submit_id = request.POST.get('submit_id', 1)
        command = request.POST.get('command', '')
        command_args = request.POST.get('command_args', '')
        user_pw = 'dbmonitor/ghsdbmon1tor'
        ##获取数据库连接串
        select_conn_string = request.POST.get('conn_string', None)
        if command_args == '':
            args = command
        else:
            args = command + ' ' + command_args
        global res_suc
        res_suc = ''
        if select_conn_string == '使用脚本内实例':
            ##使用脚本内的实例
            conn_string = '使用脚本内实例'
            # exec_command_text = 'sh /home/oracle/dbmonitor/ora ' + args
            command = 'sh /home/oracle/dbmonitor/ora ' + args

        else:
            ##获取数据库名字
            dbname = select_conn_string.split('=')[0]
            ##获取数据库连接串
            conn_string = select_conn_string.split('=')[1]
            ##拼装命令
            command = 'sh /home/oracle/dbmonitor/ora -u ' + user_pw + '@' + conn_string + ' ' + args

        try:
            ##ssh连接
            client = paramiko.SSHClient()
            client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            ##连接服务器
            client.connect(hostname='12.18.0.16', port='22', username='oracle', password='oracle')
            ##拼装命令
            # command ='sh /home/oracle/dbmonitor/ora -u ' + user_pw +'@'+ conn_string + ' ' + args
            print(command)
            stdin, stdout, stderr = client.exec_command(str(command))
            ##命令执行结果,readlines是将结果弄成列表方式
            res_suc = str(stdout.read(), 'utf-8')
            # print(res_suc)
            res_err = str(stderr.read(), 'utf-8')
            print(res_err)
            client.close()
            ##ajax post提交
        except Exception as e:
            res_err = e
        if submit_id == '0':
            return render(request, 'oratk_app/oratk_table_ora_shell.html', {'res_suc': res_suc})
        ##form post提交方式
        else:
            return render(request, 'oratk_app/oratk_select_use_ora_shell.html',
                          {'res_suc': res_suc, 'conn_string': conn_string, 'args': args})


## 等待事件与锁
def oratk_most_waitevent_lock(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)

            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
            return render(request, 'oratk_app/oratk_most_event.html', {'instanceinfo_result': instanceinfo_result})
        else:
            return redirect('/basic_app/login.html')


# 执行计划
def oratk_most_plan(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)

            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
            return render(request, 'oratk_app/oratk_most_plan.html', {'instanceinfo_result': instanceinfo_result})
        else:
            return redirect('/basic_app/login.html')


###常用功能-执行计划 post与ajax
def oratk_most_plan_ajax(request):
    global select_conn_string
    global columns
    global result
    global dbname
    global conn_string
    global arg1
    global arc_c
    if request.method == 'POST':
        args_dic = {}
        submit_id = request.POST.get('submit_id', 1)
        ##获取数据库连接串
        select_conn_string = request.POST.get('conn_string', None)
        ##获取数据库名字
        dbname = select_conn_string.split('=')[0]
        ##获取数据库连接串
        conn_string = select_conn_string.split('=')[1]
        ##db连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()

        sql_keyword = request.POST.get('sql_keyword', None)
        sql_id = request.POST.get('sql_id', None)
        if len(sql_id) != 0 and len(sql_keyword) == 0:
            arg1 = sql_id
            tool_name = 'sqlxp_sqlid'
        else:
            arg1 = sql_keyword
            tool_name = 'sqltext_text'
        print(arg1)
        print(tool_name)
        # ##获取查询项目对应的sql
        # ##sql.sqltext 为对应的sql
        sql = cron_models.ora_point_info.objects.filter(name=tool_name).first()
        sqltextlist = ast.literal_eval(sql.sqltext.replace('\r', '').replace('\n', ''))
        print(sqltextlist)

        args_dic['select_name'] = tool_name
        args_dic['dbname'] = dbname
        args_dic['select_conn_string'] = select_conn_string
        args_dic['conn_string'] = conn_string
        arc_c = 0
        for i in sqltextlist:
            args_dic['desc' + str(arc_c)] = i[0] + ':'
            # 替换sql
            sqltext = i[1].replace(':1', arg1)
            cursor.execute(sqltext)
            ##列名
            args_dic['columns' + str(arc_c)] = [col[0] for col in cursor.description]
            ##获取结果
            # result = cursor.fetchall()
            args_dic['result' + str(arc_c)] = cursor.fetchall()

            arc_c = arc_c + 1

        if submit_id == '0':
            return render(request, 'oratk_app/oratk_table_ora.html', args_dic)
        else:
            return render(request, 'oratk_app/oratk_select_use_ora.html', args_dic)

    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')


###常用功能-等待事件 post与ajax
def oratk_most_event_ajax(request):
    global select_conn_string
    global columns
    global result
    global dbname
    global conn_string
    global arg1
    global arg2
    global arc_c
    global sqllist
    global args_dic
    if request.method == 'POST':
        sid = request.POST.get('sid', None)
        ins_id = request.POST.get('ins_id', None)
        args_dic = {}
        submit_id = request.POST.get('submit_id', 1)

        box_session_event_check = request.POST.get('box_session_event_check', None)
        box_lock_info_check = request.POST.get('box_lock_info_check', None)
        box_killsql_info_check = request.POST.get('box_killsql_info_check', None)
        ##获取数据库连接串
        select_conn_string = request.POST.get('conn_string', None)
        ##获取数据库名字
        dbname = select_conn_string.split('=')[0]
        ##获取数据库连接串
        conn_string = select_conn_string.split('=')[1]
        ##db连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()

        args_dic['dbname'] = dbname
        args_dic['select_conn_string'] = select_conn_string
        args_dic['conn_string'] = conn_string
        arc_c = 0
        if len(sid) > 0:
            args_dic['select_name'] = 'session_detail'
            # 当实例没有输入时，默认值为1
            if len(ins_id) == 0:
                arg2 = '1'
            else:
                arg2 = ins_id
            # ##获取查询项目对应的sql
            # ##sql.sqltext 为对应的sql
            # 当查询客户端信息时，获取sql语句
            sql_session_detail = cron_models.ora_point_info.objects.filter(name='session_detail').first()
            sql_session_detail = ast.literal_eval(sql_session_detail.sqltext.replace('\r', '').replace('\n', ''))
            sqllist = sql_session_detail
            for i in sqllist:
                args_dic['desc' + str(arc_c)] = i[0] + ':'
                arg1 = sid
                # 获取sql文本
                sqltext = i[1].replace(':1', arg1).replace(':2', arg2)
                print(sqltext)
                cursor.execute(sqltext)
                ##列名
                args_dic['columns' + str(arc_c)] = [col[0] for col in cursor.description]
                ##获取结果
                # result = cursor.fetchall()
                args_dic['result' + str(arc_c)] = cursor.fetchall()

                arc_c = arc_c + 1
            if submit_id == '0':
                return render(request, 'oratk_app/oratk_table_ora_session.html', args_dic)
            else:
                return render(request, 'oratk_app/oratk_select_use_ora.html', args_dic)

        else:

            # 根据前台所选项获取要查询的sql语句

            sql_date = cron_models.ora_point_info.objects.filter(name='cur_date').first()
            sql_date = ast.literal_eval(sql_date.sqltext.replace('\r', '').replace('\n', ''))
            sql_event_stats = cron_models.ora_point_info.objects.filter(name='event_stats').first()
            sql_event_stats = ast.literal_eval(sql_event_stats.sqltext.replace('\r', '').replace('\n', ''))

            sqllist = sql_date + sql_event_stats
            select_name = 'current_date event_stats'
            if  box_session_event_check:
                sql_event_overview = cron_models.ora_point_info.objects.filter(name='event_overview').first()
                sql_event_overview = ast.literal_eval(sql_event_overview.sqltext.replace('\r', '').replace('\n', ''))
                sqllist = sqllist + sql_event_overview
                select_name = select_name + ' event_overview'
            if  box_lock_info_check:
                sql_lock = cron_models.ora_point_info.objects.filter(name='lock').first()
                sql_lock = ast.literal_eval(sql_lock.sqltext.replace('\r', '').replace('\n', ''))
                sqllist = sqllist + sql_lock
                select_name = select_name  + ' lock_chain_info'
            if  box_killsql_info_check:
                sql_session_detail_lock = cron_models.ora_point_info.objects.filter(name='session_detail_lock').first()
                sql_session_detail_lock = ast.literal_eval(sql_session_detail_lock.sqltext.replace('\r', '').replace('\n', ''))
                sqllist = sqllist + sql_session_detail_lock
                select_name = select_name + ' breaker_info && killsql'
            args_dic['select_name'] = select_name
            for i in sqllist:
                args_dic['desc' + str(arc_c)] = i[0] + ':'
                # 获取sql文本
                sqltext = i[1]
                #print(i[1])
                cursor.execute(sqltext)
                ##列名
                args_dic['columns' + str(arc_c)] = [col[0] for col in cursor.description]
                ##获取结果
                # result = cursor.fetchall()
                args_dic['result' + str(arc_c)] = cursor.fetchall()

                arc_c = arc_c + 1
            if submit_id == '0':
                return render(request, 'oratk_app/oratk_table_ora.html', args_dic)
            else:
                return render(request, 'oratk_app/oratk_select_use_ora.html', args_dic)

    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')


##常用功能-日志挖掘
def oratk_most_logmnr(request):
    url = '/oratk_app/oratk_most_logmnr_ajax_submit'
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
            # 用户搜索时：
            if user_search != 'all':
                logmnr_log_result = oratk_models.oratk_most_logmnr_record.objects.filter(
                    Q(rec_date__contains=user_search)
                    | Q(db_name__contains=user_search)
                    | Q(db_conn__contains=user_search)
                    | Q(run_table__contains=user_search)
                    | Q(status__contains=user_search)).order_by('-rec_date')
            # 当用户没有搜索时：
            else:
                user_search = ''
                logmnr_log_result = oratk_models.oratk_most_logmnr_record.objects.all().order_by('-rec_date')
            v_paginator(logmnr_log_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_most_logmnr.html',
                          {'instanceinfo_result': instanceinfo_result, 'current_page': current_page,
                           'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search,'url':url})
        else:
            return redirect('/basic_app/login.html')


def oratk_most_logmnr_ajax(request):
    if request.method == 'POST':
        ##获取数据库连接串
        select_conn_string = request.POST.get('conn_string', None)
        ##获取数据库名字
        dbname = select_conn_string.split('=')[0]
        ##获取数据库连接串
        conn_string = select_conn_string.split('=')[1]
        ##时间段
        run_date_range = request.POST.get('run_date_range', None)
        ##session信息
        session_info = request.POST.get('session_info', None)
        ##对象信息
        object_info = request.POST.get('object_info', None)
        ##事务信息
        tran_info = request.POST.get('tran_info', None)
        try:
            db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
            cursor = db.cursor()
            cursor_supplemental = db.cursor()
            ##列出时间段内相关redo或archivelog
            sqltext = '''
            select type,thread#,first_time,next_time,sequence#,
            row_number() over(order by thread#,sequence# ) as rn,name,size_mb,status 
      from (select 'ARCH' as type ,thread#, first_time, next_time, sequence#, name,
                   round(blocks * block_size / 1024 / 1024) as size_mb,'DELETED:'||deleted as status
              from v$archived_log
             where dest_id = 1 and name is not null AND next_time > to_date('action_time', 'YYYY-MM-DD HH24:MI:SS') and first_time <= to_date('end_time', 'YYYY-MM-DD HH24:MI:SS')
            union
            select 'REDO', l.thread#, l.first_time, l.next_time, l.sequence#, f.member name,
                   null as size_mb ,l.status
              from v$log l
              join v$logfile f on f.group# = l.group#
               and f.is_recovery_dest_file = 'NO' and l.archived = 'NO' AND first_time <= to_date('end_time', 'YYYY-MM-DD HH24:MI:SS')   ) a
     order by thread#, sequence# 
            '''
            sqltext_supplemental = '''
            select  SUPPLEMENTAL_LOG_DATA_MIN  from v$database 
            '''
            if run_date_range is not None:
                action_time = run_date_range.split('/')[0]
                end_time = run_date_range.split('/')[1]
                sqltext = sqltext.replace('action_time', action_time).replace('end_time', end_time)
                # print(sqltext)
            ##查询日志信息
            cursor.execute(sqltext)
            ##列名
            columns = [col[0] for col in cursor.description]
            # print (columns)
            ##一条记录为一个字典,结果集为列表
            # cursor.rowfactory = lambda *args: dict(zip(columns, args))
            ##获取结果
            result = cursor.fetchall()
            ##查询附加日志
            cursor_supplemental.execute(sqltext_supplemental)
            ##列名
            columns_supplemental = [col[0] for col in cursor_supplemental.description]
            # print (columns)
            ##一条记录为一个字典,结果集为列表
            # cursor.rowfactory = lambda *args: dict(zip(columns, args))
            ##获取结果
            result_supplemental = cursor_supplemental.fetchall()

            return render(request, 'oratk_app/oratk_most_logmnr_ajax_table.html',
                          {'result': result, 'columns': columns, 'run_date_range': run_date_range,
                           'columns_supplemental': columns_supplemental, 'result_supplemental': result_supplemental})
        except Exception as e:
            return HttpResponse(e)

    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')


def oratk_most_logmnr_ajax_submit(request):
    if request.method == 'POST':
        currentpage_num = int(request.POST.get('page', '1'))
        line_count_set = int(request.POST.get('line_count', 5))
        user_search = request.POST.get('user_search', 'all')
        url = '/oratk_app/oratk_most_logmnr_ajax_submit'

        logmnr_exec_status = request.POST.get('logmnr_exec_status', None)
        if logmnr_exec_status == 'YES':
            # 获取当前时间
            curr_time_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            ##获取数据库连接串
            select_conn_string = request.POST.get('conn_string', None)
            ##获取数据库名字
            dbname = select_conn_string.split('=')[0]
            ##获取数据库连接串
            conn_string = select_conn_string.split('=')[1]
            ##时间段
            run_date_range = request.POST.get('run_date_range', None)
            ##开始/结束时间
            action_time = run_date_range.split('/')[0].strip()
            end_time = run_date_range.split('/')[1].strip()
            ##session信息
            session_info = request.POST.get('session_info', None)
            ##对象信息
            object_info = request.POST.get('object_info', None)
            ##事务信息
            tran_info = request.POST.get('tran_info', None)
            try:
                db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
                cursor1 = db.cursor()
                cursor2 = db.cursor()
                ##挖掘命令
                logmnr_text = '''
                begin
                DBMS_LOGMNR.START_LOGMNR(options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE,starttime => TO_DATE('action_time','YYYY-MM-DD HH24:MI:SS'),endtime => TO_DATE('end_time','YYYY-MM-DD HH24:MI:SS'));
                end;
                '''

                ##实例化表
                ##存放实例化表条件
                info_where = {}
                ##执行时条件
                info_where_str = ' where '
                ##实例化条件（记录到表）
                info_where_str_2 = ''
                if session_info != '':
                    session_id = session_info.split(',')[0]
                    serial_id = session_info.split(',')[1]
                    info_where['session_info'] = 'session# = %s and serial# = %s' % (session_id, serial_id)
                if object_info != '':
                    object_info = object_info.upper()
                    info_where['object_info'] = "seg_name = '%s'" % (object_info)
                if tran_info != '':
                    info_where['tran_info'] = "xid = '%s'" % (tran_info)
                if not info_where:
                    create_tab = '''
                     create table logmnr_contents_curr_time as select * from v$logmnr_contents
                    '''
                else:
                    i = 0
                    for key, value in info_where.items():
                        dic_info = key + ": " + value
                        if i == 0:
                            info_where_str = info_where_str + value
                            # 记录条件
                            info_where_str_2 = info_where_str_2 + dic_info
                        else:
                            info_where_str = info_where_str + ' or ' + value
                            # 记录条件
                            info_where_str_2 = info_where_str_2 + '\t' + dic_info
                        i = i + 1
                    create_tab = '''
                                     create table logmnr_contents_curr_time as select * from v$logmnr_contents
                                    '''
                    create_tab = create_tab + info_where_str
                print('实例化条件：' + info_where_str_2)
                ##执行挖掘命令
                logmnr_text = logmnr_text.replace('action_time', action_time).replace('end_time', end_time)
                print(logmnr_text)
                # cursor2.callproc(logmnr_text)
                format_date = "ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'"
                # cursor2.execute(format_date)
                cursor2.execute(logmnr_text)
                ##执行实例名表
                curr_time = time.strftime('%d_%H_%M', time.localtime(time.time()))
                create_tab = create_tab.replace('curr_time', curr_time)
                print('实例化sql：' + create_tab)
                cursor2.execute(create_tab)
                ## 获取条目数
                tab_count = 'select count(*) from logmnr_contents_curr_time'
                tab_count = tab_count.replace('curr_time', curr_time)
                cursor2.execute(tab_count)
                result_count = cursor2.fetchall()
                result_count = str(result_count[0][0])
                ##表名
                tab_name = 'dbmonitor.' + 'logmnr_contents_' + curr_time
                status = 'success'
                msg_err = 'success'
                oratk_models.oratk_most_logmnr_record.objects.create(rec_date=curr_time_str, db_name=dbname,
                                                                     db_conn=conn_string,
                                                                     run_date_range=run_date_range,
                                                                     run_condition=info_where_str_2,
                                                                     run_table=tab_name,
                                                                     table_count=result_count,
                                                                     status=status,
                                                                     msg_err=msg_err
                                                                     )
            except Exception as e:
                print(e)
                status = 'fail'
                oratk_models.oratk_most_logmnr_record.objects.create(rec_date=curr_time_str, db_name=dbname,
                                                                     db_conn=conn_string,
                                                                     run_date_range=run_date_range,
                                                                     status=status,
                                                                     msg_err=e
                                                                     )
        #instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
        # 用户搜索时：
        if user_search != 'all':
            logmnr_log_result = oratk_models.oratk_most_logmnr_record.objects.filter(
                Q(rec_date__contains=user_search)
                | Q(db_name__contains=user_search)
                | Q(db_conn__contains=user_search)
                | Q(run_table__contains=user_search)
                | Q(status__contains=user_search)).order_by('-rec_date')
        # 当用户没有搜索时：
        else:
            user_search = ''
            logmnr_log_result = oratk_models.oratk_most_logmnr_record.objects.all().order_by('-rec_date')
        v_paginator(logmnr_log_result, currentpage_num, line_count_set)
        # return redirect('/oratk_app/oratk_most_logmnr')
        return render(request, 'oratk_app/oratk_tab_most_logmnr.html',
                      {'current_page': current_page,
                       'paginator': paginator, 'line_count': line_count_set,
                       'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                       'paginator_num_pages_list': paginator_num_pages_list
                          , 'search': user_search, 'url': url})
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')


##常用功能-Archery 工单查询
def oratk_most_archery_select(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
            return render(request, 'oratk_app/oratk_most_archery_select.html',
                          {'instanceinfo_result': instanceinfo_result})
        else:
            return redirect('/basic_app/login.html')


from pymysql.converters import escape_string


def oratk_most_archery_select_ajax(request):
    if request.method == 'POST':
        # select_id = random.sample(range(0, 999999), 1)[0]
        key_info_or = request.POST.get('key_info_or', None)
        key_info_and = request.POST.get('key_info_and', None)
        ##where 条件
        where_condition_list_or = []
        where_condition_list_and = []
        #where_condition = 'a1.sql_content like '%%%s%%''
        if  key_info_or is not None:
            key_info_or = key_info_or.strip().split()
            for i in key_info_or:
                where_condition_or = '''
            a1.sql_content like '%%%s%%'
            '''%(i)
                where_condition_list_or.append(where_condition_or)
            where_condition_or = " or ".join(where_condition_list_or)
        if key_info_and is not None:
            key_info_and = key_info_and.strip().split()
            for i in key_info_and:
                where_condition_and = '''
            a1.sql_content like '%%%s%%'
            '''%(i)
                where_condition_list_and.append(where_condition_and)
            where_condition_and = " and ".join(where_condition_list_and)
        ##拼装where 条件
        # print('oroororor_____ ',where_condition_list_or)
        # print('andandand_____ ',where_condition_list_or)
        if not where_condition_list_or:
            print(1)
            where_condition = where_condition_and
        else:
            if  not where_condition_list_and:
                print(2)
                where_condition = where_condition_or
            else:
                print(3)
                where_condition = ' ( ' + where_condition_or  + ' ) ' + ' and ' + where_condition_and
        ##拼装sql
        select_sql = "SELECT a2.engineer_display,a2.workflow_name,a2.create_time create_time,CONCAT('http://12.18.0.18:9123/detail/',a1.id) FROM sql_workflow_content a1 left join sql_workflow a2 on a2.id=a1.id where"   + where_condition  + "order by create_time desc"
        print(select_sql)
        # 连接mysql
        db = pymysql.connect(host='12.18.0.18', port=3306, user='archery', passwd='ghstestarchery', db='archery')
        # 获取mysql操作光标
        sql_cursor = db.cursor()
        sql_cursor_select = db.cursor()
        # 执行sql
        ##插入条件信息
        # sql_cursor.execute(insert_sql)
        # db.commit()
        sql_cursor.execute(select_sql)
        # 获取结果
        res_info = sql_cursor.fetchall()
        if not res_info:
            print(1)
            return HttpResponse('空')
        else:
            print(2)
            return render(request, 'oratk_app/oratk_table_select_archery.html', {'res_info': res_info})

    if request.method == 'GET':
        pass


#########sqlldr功能##############
###ajax 上传文件
def oratk_most_sqlldr_ajax(request):
    if request.method == 'POST':
        curr_time_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        try:
            obj_file = request.FILES.get('uploadfile_name')
            # print()
            # BASE_DIR = Path(__file__).resolve().parent.parent
            # 上传文件的文件名
            # print(obj.name)
            try:
                file_extname = obj_file.name.split('.')[1]
            except Exception as e:
                return HttpResponse("ERROR")
            if file_extname == 'txt' or file_extname == 'csv' or file_extname == 'xlsx' or file_extname == 'xls':
                if file_extname == 'xlsx' or file_extname == 'xls':
                    dir = 'sqlldr_excel'
                else:
                    dir = 'sqlldr_csv'
                f = open(os.path.join(BASE_DIR, 'templates', dir, obj_file.name), 'wb')
                ##获取文件大小
                file_len = 0
                for chunk in obj_file.chunks():
                    f.write(chunk)
                    file_len_each = len(chunk)
                    file_len = file_len_each + file_len
                f.close()
                # file_size = os.path.getsize(os.path.join(BASE_DIR, 'templates', 'sqlldr_excel', obj_file.name))
                # file_size = str(round(file_len/1024,2)) + 'KB'
                if file_len // (1024 * 1024) > 0:
                    file_size = str(round(file_len / 1024 / 1024, 2)) + ' MB'
                elif file_len // (1024) > 0:
                    file_size = str(round(file_len / 1024, 2)) + ' KB'
                else:
                    file_size = str(file_len) + ' B'
                file_path = os.path.join(BASE_DIR, 'templates', dir)
                file_name = obj_file.name
                file_result = os.path.join(file_path, file_name)
                file_make = 'upload'
                if file_extname == 'xlsx' or file_extname == 'xls':
                    print("upload: 'xlsx,xls'")
                    status = 1
                    wb = openpyxl.load_workbook(file_result)
                    # sheets = wb.get_sheet_names()
                    sheets = wb.sheetnames
                    file_sheets = sheets
                    sheets_count = len(sheets)
                    content_count = {}
                    for i in wb.sheetnames:
                        ws = wb[i]
                        sheet_count = ws.max_row
                        content_count[i] = sheet_count
                if file_extname == 'csv' or file_extname == 'txt':
                    print("upload: 'csv or txt'")
                    df = pd.read_csv(file_result, encoding='utf-8', delimiter="\t", )
                    print(1)
                    content_count = df.shape[0]
                    print(2)
                    status = 0
                    sheets_count = ''
                    file_sheets = ''

                oratk_models.oratk_most_sqlldr_uploadfile.objects.filter(file_name=obj_file.name).delete()
                oratk_models.oratk_most_sqlldr_uploadfile.objects.create(rec_date=curr_time_str, file_name=file_name,
                                                                         file_path=file_path, file_size=file_size,
                                                                         file_mode=file_extname, status=status,
                                                                         file_sheets_count=sheets_count,
                                                                         file_sheets=file_sheets,
                                                                         file_make=file_make,
                                                                         content_count=content_count
                                                                         )
                # return render(request, 'oratk_app/oratk_most_sqlldr.html')
                return HttpResponse("OK")
            else:
                return HttpResponse("ERROR")
        except Exception as e:
            print(e)
            return HttpResponse(e)


def oratk_most_sqlldr(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        currentpage_num = int(request.GET.get('page', '1'))
        line_count_set = int(request.GET.get('line_count', 5))
        user_search = request.GET.get('user_search', 'all')
        currentpage_num_2 = int(request.GET.get('page_2', '1'))
        line_count_set_2 = int(request.GET.get('line_count_2', 5))
        user_search_2 = request.GET.get('user_search_2', 'all')
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
            file_info_result = oratk_models.oratk_most_sqlldr_uploadfile.objects.all().order_by('-rec_date')
            make_file_result_xlsx = oratk_models.oratk_most_sqlldr_uploadfile.objects.filter(file_mode='xlsx').order_by(
                'file_name')
            make_file_result = oratk_models.oratk_most_sqlldr_uploadfile.objects.filter(~Q(file_mode='xlsx')).order_by(
                'file_name')
            control_name_list = oratk_models.oratk_most_sqlldr_controlfile.objects.values('control_name')
            sqlldr_exec_record = oratk_models.oratk_most_sqlldr_record.objects.all().order_by('-ctime')
            v_paginator(file_info_result, currentpage_num, line_count_set)
            v_paginator_2(sqlldr_exec_record, currentpage_num_2, line_count_set_2)
            return render(request, 'oratk_app/oratk_most_sqlldr.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search, 'make_file_result_xlsx': make_file_result_xlsx,
                           'make_file_result': make_file_result
                              , 'instanceinfo_result': instanceinfo_result, 'control_file_list': control_name_list,
                           'sqlldr_exec_record': sqlldr_exec_record,
                           'current_page_2': current_page_2, 'paginator_2': paginator_2,
                           'line_count_2': line_count_set_2,
                           'page_range_2': page_range_2, 'current_page_num_2': currentpage_num_2,
                           'paginator_num_pages_list_2': paginator_num_pages_list_2
                              , 'search_2': user_search_2})
        else:
            return redirect('/basic_app/login.html')


##下载文件
def oratk_most_sqlldr_download(request):
    if request.method == "GET":
        nid = request.GET.get('nid')
        file_info = oratk_models.oratk_most_sqlldr_uploadfile.objects.get(id=nid)
        # print(file_info,type(file_info))
        file_path = file_info.file_path
        file_name = file_info.file_name
        file = file_path + '/' + file_name
        file = open(file, 'rb')
        response = HttpResponse(file)
        response['Content-Type'] = 'application/octet-stream'  # 设置头信息，告诉浏览器这是个文件
        response['Content-Disposition'] = 'attachment;filename="' + file_name + '"'
        return response


##删除记录和文件
def oratk_most_sqlldr_delete(request):
    if request.method == 'GET':
        delete_mode = request.GET.get('delete_tag')
        nid = request.GET.get('nid')
        if delete_mode == 'sqlldr_file':
            file_info = oratk_models.oratk_most_sqlldr_uploadfile.objects.get(id=nid)
            # print(file_info,type(file_info))
            file_path = file_info.file_path
            file_name = file_info.file_name
            file = file_path + '/' + file_name
            try:
                os.remove(file)
            except Exception as e:
                print(e)
            oratk_models.oratk_most_sqlldr_uploadfile.objects.filter(id=nid).delete()
        if delete_mode == 'sqlldr_record':
            try:
                file_info = oratk_models.oratk_most_sqlldr_record.objects.get(id=nid)
                file_name_date = str(file_info.control_file_name).split('.')[0]
                print(file_name_date)
                file_path = str(file_info.file_path)
                ##删除sqlldr_log的记录
                try:
                    oratk_models.oratk_most_sqlldr_log_file.objects.filter(
                        Q(file_name__contains=file_name_date)).delete()
                except Exception as e:
                    print(e)
                ##删除crl log bad 文件
                control_file = file_path + '/' + file_name_date + '.ctl'
                try:
                    os.remove(control_file)
                except Exception as e:
                    print(e)
                log_file = file_path + '/' + file_name_date + '.log'
                try:
                    os.remove(log_file)
                except Exception as e:
                    print(e)
                bad_file = file_path + '/' + file_name_date + '.bad'
                try:
                    os.remove(bad_file)
                except Exception as e:
                    print(e)
                ##删除sqlldr_record的记录
                oratk_models.oratk_most_sqlldr_record.objects.filter(id=nid).delete()
            except Exception as e:
                print(e)
        return redirect('/oratk_app/oratk_most_sqlldr')


##解析文件中的特殊字符
import re
import pandas as pd


def oratk_most_sqlldr_parse_ajax(request):
    curr_time_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    if request.method == 'POST':
        # 判断是否有特殊字符
        string = ''' 
        ~!@#$%^&*()'"_+-*/<>,[]/
        '''
        print('oratk_most_sqlldr_parse_ajax')
        file_name = request.POST.get('file_name', None)
        file_sheet = request.POST.get('file_sheet', None)
        file_result = os.path.join(BASE_DIR, 'templates', 'sqlldr_excel', file_name)
        print(file_result, file_sheet)
        wb = openpyxl.load_workbook(file_result)  # 首先获取excel文件生成对象wb 使用openpyxl.load_workbook 方法
        # print(file_sheet)
        # if file_sheet.replace('\xa0',' ') == 'She et3':
        #     print(file_sheet.replace('\xa0',' '))
        #     print('aaa')
        # else:
        #     print(file_sheet.replace('\xa0',' '))
        #     print('bbb')
        ws = wb[file_sheet.replace('\xa0', ' ')]
        # ws = wb['She  et3']
        special_str_res = []
        special_str_side = []
        ##普通方式查找单元格内容里是否包含特殊字符
        for row in ws.rows:
            for cell in row:
                seach_count = 1
                for i in string:
                    seach_count = 1
                    if i in str(cell.value):
                        special_str_res.append(i)
                        if cell.value[0] == i or cell.value[len(cell.value) - 1] == i:
                            special_str_side.append(i)
        sort_special_str_res = list(set(special_str_res))
        sort_special_str_side = list(set(special_str_side))
        print(sort_special_str_res, sort_special_str_side)
        total_count = ws.max_row
        if len(sort_special_str_res) == 0:
            sort_special_str_res = '无'
        if len(sort_special_str_side) == 0:
            sort_special_str_side = '无'
        # ~!@  # $%^&*()'"_+-*/<>,.[]/
        cut_str = ['!', ',', '#', '%', '$', '*', '+', '@', '_', '-', '~', '^', '/', '\\', '"']
        valid_str = list(set(cut_str) - set(special_str_res))
        print(valid_str)
        include_str = []
        return render(request, 'oratk_app/oratk_tab_most_sqlldr_parse_ajax.html',
                      {'special_str': sort_special_str_res, 'special_str_side': sort_special_str_side,
                       'total_count': total_count, 'valid_str': valid_str})


##获取excel中的sheet列表
def oratk_most_sqlldr_sheet_ajax(request):
    curr_time_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    if request.method == 'POST':
        print('oratk_most_sqlldr_sheet_ajax')
        file_name = request.POST.get('file_name', None)
        # 获取工作薄
        file_info = oratk_models.oratk_most_sqlldr_uploadfile.objects.get(file_name=file_name)
        print(file_info.file_sheets, type(file_info))
        sheets = ast.literal_eval(file_info.file_sheets)
        sheets_list = []
        for i in sheets:
            i = i.replace(' ', '&nbsp;')
            sheets_list.append(i)
        return render(request, 'oratk_app/oratk_tab_most_sqlldr_sheet_ajax.html', {'sheet_list': sheets_list})


## 将excel转换成csv
def oratk_most_sqlldr_convert_csv_ajax(request):
    curr_time_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    curr_time_str_2 = time.strftime('%H%M%S', time.localtime(time.time()))
    if request.method == 'POST':
        try:
            file_name = request.POST.get('file_name', None)
            file_sheet = request.POST.get('sheet_name', None)
            file_sheet = file_sheet.replace('\xa0', ' ')
            file_csv_name = file_name.split('.')[0] + '_' + file_sheet + '_'+ curr_time_str_2 + '.csv'
            file_result = os.path.join(BASE_DIR, 'templates', 'sqlldr_excel', file_name)
            file_result_csv = os.path.join(BASE_DIR, 'templates', 'sqlldr_csv', file_csv_name)
            data_xls = pd.read_excel(file_result, index_col=0, sheet_name=file_sheet)
            print('oratk_most_sqlldr_convert_csv_ajax')
            print(file_name, file_sheet, file_result_csv)
            ##excel转换成csv
              ##float_format='%.0f'  不保留小数 来解决科学记录法问题
            data_xls.to_csv(file_result_csv, encoding='utf-8')
            ##读取csv header=None 忽略表头
            df = pd.read_csv(file_result_csv, encoding='utf-8', header=None)
            ##获取文件大小
            file_size = os.path.getsize(file_result_csv)
            print(file_size)
            if file_size // (1024 * 1024) > 0:
                file_size = str(round(file_size / 1024 / 1024, 2)) + ' MB'
            elif file_size // (1024) > 0:
                file_size = str(round(file_size / 1024, 2)) + ' KB'
            else:
                file_size = str(file_size) + ' B'
            ##csv行数
            content_count = df.shape[0]
            ##csv名字
            file_name = file_csv_name
            ##csv路径
            file_path = os.path.join(BASE_DIR, 'templates', 'sqlldr_csv')
            file_make = 'convert_csv'
            file_mode = 'csv'
            oratk_models.oratk_most_sqlldr_uploadfile.objects.create(rec_date=curr_time_str, file_name=file_name,
                                                                     file_path=file_path, file_make=file_make,
                                                                     file_mode=file_mode,
                                                                     file_size=file_size,
                                                                     content_count=content_count
                                                                     )
            return HttpResponse('ok')
        except Exception as e:
            print('e')
            return HttpResponse(e)


## 将excel 根据规则手工格式化
def oratk_most_sqlldr_manual_format_ajax(request):
    curr_time_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    curr_time_str_2 = time.strftime('%H%M%S', time.localtime(time.time()))
    if request.method == 'POST':
        try:
            file_name = request.POST.get('file_name', None)
            file_sheet = request.POST.get('sheet_name', None)
            file_sheet = file_sheet.replace('\xa0', ' ')
            cut_str = request.POST.get('cut_str', None)
            quote_str = request.POST.get('quote_str', None)
            head_str = request.POST.get('head_str', None)
            # quote_str = "\" + quote_str
            manual_file_name = file_name.split('.')[0] + '_' + file_sheet + '_manual_' + curr_time_str_2 + '.txt'
            file_result = os.path.join(BASE_DIR, 'templates', 'sqlldr_excel', file_name)
            manual_file = os.path.join(BASE_DIR, 'templates', 'sqlldr_csv', manual_file_name)
            #data_xls = pd.read_excel(file_result, index_col=0, sheet_name=file_sheet,dtype=object)
            if head_str == '否':
                print('无表头')
                data_xls = pd.read_excel(file_result,index_col=0,sheet_name=file_sheet,dtype=object,header=None)
                print('oratk_most_sqlldr_manual_format_ajax')
                print(file_name, file_sheet, manual_file)
                ##excel转换成csv
                print('开始转换')
                print(cut_str,quote_str)
                data_xls.to_csv(manual_file,encoding='utf-8', sep=cut_str, quotechar=quote_str,header=None)
                print('转换完成')
            else:
                print('有表头')
                data_xls = pd.read_excel(file_result, index_col=0, sheet_name=file_sheet, dtype=object)
                print('oratk_most_sqlldr_manual_format_ajax')
                print(file_name, file_sheet, manual_file)
                ##excel转换成csv
                print('开始转换')
                print(cut_str, quote_str)
                data_xls.to_csv(manual_file, encoding='utf-8', sep=cut_str, quotechar=quote_str)
                print('转换完成')
            # 文件比较大
            count = -1
            for count, line in enumerate(open(manual_file, 'rU')):
                pass
                count += 1
            # df = pd.read_csv(manual_file, encoding='utf-8', header=None,error_bad_lines=False)
            ##获取文件大小
            file_size = os.path.getsize(manual_file)
            if file_size // (1024 * 1024) > 0:
                file_size = str(round(file_size / 1024 / 1024, 2)) + ' MB'
            elif file_size // (1024) > 0:
                file_size = str(round(file_size / 1024, 2)) + ' KB'
            else:
                file_size = str(file_size) + ' B'
            ##csv行数
            # content_count = df.shape[0]
            content_count = count
            ##txt名字
            file_name = manual_file_name
            ##txt路径
            file_path = os.path.join(BASE_DIR, 'templates', 'sqlldr_csv')
            file_make = 'manual_format'
            file_mode = 'txt'
            # format_info = 'sep:'+ cut_str + ',quote:' + quote_str
            format_info = '''
            {'%s':'%s','%s':'%s'}
            ''' % ('sep', cut_str, 'quote', quote_str)
            print(format_info)
            oratk_models.oratk_most_sqlldr_uploadfile.objects.filter(file_name=file_name).delete()
            oratk_models.oratk_most_sqlldr_uploadfile.objects.create(rec_date=curr_time_str, file_name=file_name,
                                                                     file_path=file_path, file_make=file_make,
                                                                     file_mode=file_mode,
                                                                     file_size=file_size,
                                                                     content_count=content_count,
                                                                     format_info=format_info
                                                                     )
            return HttpResponse('ok')
        except Exception as e:
            print(e)
            return HttpResponse(e)


def oratk_most_sqlldr_control_file_ajax(request):
    curr_time_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    if request.method == 'POST':
        print('oratk_most_sqlldr_control_file_ajax')
        control_name = request.POST.get('control_name', None)
        # 获取工作薄
        control_content = oratk_models.oratk_most_sqlldr_controlfile.objects.get(control_name=control_name)
        # control_content = ast.literal_eval(control_content.control_content)
        return render(request, 'oratk_app/oratk_tab_most_sqlldr_control_ajax.html',
                      {'control_content': control_content})


def oratk_most_sqlldr_exec_ajax(request):
    curr_time_str = time.strftime('%Y-%m-%d_%H-%M', time.localtime(time.time()))
    curr_time_str_1 = time.strftime('%Y-%m-%d %H:%M', time.localtime(time.time()))
    sqlldr_exec_path = '/Users/liwk/py/oratk_web/templates/sqlldr_log'
    sqlldr_ctl = 'sqlldr' + curr_time_str + '.ctl'
    sqlldr_log = 'sqlldr' + curr_time_str + '.log'
    sqlldr_bad = 'sqlldr' + curr_time_str + '.bad'
    sqlldr_ctl_path = '/Users/liwk/py/oratk_web/templates/sqlldr_log/' + sqlldr_ctl
    sqlldr_log_path = '/Users/liwk/py/oratk_web/templates/sqlldr_log/' + sqlldr_log
    sqlldr_bad_path = '/Users/liwk/py/oratk_web/templates/sqlldr_log/' + sqlldr_bad

    if request.method == 'POST':
        print('oratk_most_sqlldr_control_file_ajax')
        # control_name = request.POST.get('control_name', None)
        conn_string = request.POST.get('conn_string', None)
        control_content = request.POST.get('control_content', None)
        instance_info = conn_string.split('=')[1]
        try:
            print(conn_string, control_content)
            ##将修改后的ora文件内容本地文件中
            with open(sqlldr_ctl_path, 'w', encoding='utf-8') as f:
                # f.write(shell_content.replace('\r\n', '\n'))
                f.write(control_content)
            ##执行sqlldr命令
            command = 'cd /Users/liwk/py/oratk_web/templates/sqlldr_log/ && sqlldr dbmonitor/ghsdbmon1tor' + '@' + instance_info + ' control=' + sqlldr_ctl + ' log=' + sqlldr_log_path + ' bad=' + sqlldr_bad_path
            print(command)
            os.system(command)
            ##耗时
            curr_time_str_2 = time.strftime('%Y-%m-%d %H:%M', time.localtime(time.time()))
            seconds = (parse(curr_time_str_2) - parse(curr_time_str_1)).seconds
            seconds = str(seconds) + 's'
            ##将执行记录插入到表
            oratk_models.oratk_most_sqlldr_record.objects.create(ins_info=instance_info, file_path=sqlldr_exec_path,
                                                                 control_file_name=sqlldr_ctl,
                                                                 log_file_name=sqlldr_log, bad_file_name=sqlldr_bad,
                                                                 ctime=curr_time_str_1, etime=seconds
                                                                 )
            ##将日志文件内容插入表
            # os.F_OK: 判断文件是否存在
            # os.R_OK: 判断文件是否可读
            # os.W_OK: 判断文件是否可以写入
            # os.X_OK: 判断文件是否有执行权限
            control_file_content = ''
            log_file_content = ''
            bad_file_content = ''
            if os.access(sqlldr_ctl_path, os.F_OK):
                control_file_content = open(sqlldr_ctl_path, mode='r')
                control_file_content = control_file_content.read()
            oratk_models.oratk_most_sqlldr_log_file.objects.create(file_name=sqlldr_ctl,
                                                                   file_content=control_file_content
                                                                   )
            if os.access(sqlldr_log_path, os.F_OK):
                log_file_content = open(sqlldr_log_path, mode='r')
                log_file_content = log_file_content.read()
            oratk_models.oratk_most_sqlldr_log_file.objects.create(file_name=sqlldr_log,
                                                                   file_content=log_file_content
                                                                   )
            if os.access(sqlldr_bad_path, os.F_OK):
                bad_file_content = open(sqlldr_bad_path, mode='r')
                bad_file_content = bad_file_content.read()
            oratk_models.oratk_most_sqlldr_log_file.objects.create(file_name=sqlldr_bad,
                                                                   file_content=bad_file_content
                                                                   )
            return HttpResponse('ok')
        except Exception as e:
            print(e)
            return HttpResponse(e)


def oratk_most_sqlldr_get_file_content(request):
    curr_time_str = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        print('oratk_most_sqlldr_get_file_content')
        file_name = request.GET.get('file_name', None)
        ##获取文件内容
        file_content = oratk_models.oratk_most_sqlldr_log_file.objects.get(file_name=file_name)
        print(file_content.file_content)
        return HttpResponse(json.dumps(file_content.file_content))


## awr & ash ##
def oratk_most_awr_ash_report(request):
    url = 'oratk_most_awr_ash_report_ajax'
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
            awr_ash_report_result = cron_models.oratk_awr_ash_report.objects.all().order_by('-crtime')
            v_paginator(awr_ash_report_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_most_awr_ash.html',
                          {'instanceinfo_result': instanceinfo_result, 'awr_ash_report_result': awr_ash_report_result,'url':url,
                           'current_page': current_page,
                           'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')


def oratk_most_awr_select_snapshot_ajax(request):
    if request.method == 'POST':
        ##获取要查询的数据库连接串
        select_conn_string = request.POST.get('awr_conn_string', None)
        snapshot_day = request.POST.get('snapshot_day', None)
        dbname = select_conn_string.split('=')[0]
        conn_string = select_conn_string.split('=')[1]

        ##获取判断是否为rac的sqltext
        sqltext_rac = cron_models.ora_point_info.objects.filter(name='ins_rac_flag').values('sqltext').first()
        print(sqltext_rac)
        sqltext_rac = ast.literal_eval(sqltext_rac['sqltext'])[0][1]
        # ast.literal_eval
        # 如果有分号去掉分号
        if sqltext_rac.rstrip()[-1] == ';':
            sqltext_rac = sqltext_rac.rstrip()[:-1]

        ##获取snapshot的sqltext
        sqltext_snapshot_val = cron_models.ora_point_info.objects.filter(name='snapshot_info').values('sqltext').first()
        sqltext_snapshot_val_eval = ast.literal_eval(sqltext_snapshot_val['sqltext'].replace('\r', '').replace('\n', ''))
        sqltext_snapshot = sqltext_snapshot_val_eval[1][1]
        ##获取snapshot配置信息的sqltext
        sqltext_snapshot_config =  sqltext_snapshot_val_eval[0][1]
        # ast.literal_eval
        # 如果有分号去掉分号
        if sqltext_snapshot.rstrip()[-1] == ';':
            sqltext_snapshot = sqltext_snapshot.rstrip()[:-1]
        if sqltext_snapshot_config.rstrip()[-1] == ';':
            sqltext_snapshot_config = sqltext_snapshot_config.rstrip()[:-1]
        sqltext_snapshot = sqltext_snapshot.replace(':1', snapshot_day)

        try:
            ##数据库连接
            db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
            cursor = db.cursor()
            cursor_snapshot = db.cursor()
            ##执行sql
            cursor.execute(sqltext_rac)
            ##列名
            columns = [col[0] for col in cursor.description]
            ##获取结果
            result = cursor.fetchall()
            result_rac = result[0][0]

            ##获取snapshot
            cursor_snapshot.execute(sqltext_snapshot)
            ##列名
            columns_snapshot = [col[0] for col in cursor_snapshot.description]
            ##获取结果
            result_snapshot = cursor_snapshot.fetchall()
            result_snapshot_total = len(result_snapshot)

            ##获取snapshot的配置信息
            cursor_snapshot.execute(sqltext_snapshot_config)
            ##列名
            columns_snapshot = [col[0] for col in cursor_snapshot.description]
            ##获取结果
            result_snapshot_config_info = cursor_snapshot.fetchall()
            result_snapshot_config_info = '''snapshot_info:{间隔:%s分钟,保留天数:%s天}'''%(result_snapshot_config_info[0][0],result_snapshot_config_info[0][1])
            if result_rac == 'TRUE':
                instance_type = 'RAC'
            elif result_rac == 'FALSE':
                instance_type = 'SINGLE'
            return render(request, 'oratk_app/oratk_most_awr_snapshot_ajax.html',
                          {'result': result_snapshot, 'snapshot_day': snapshot_day,
                           'result_snapshot_total': result_snapshot_total, 'instance_type': instance_type,'snapshot_config':result_snapshot_config_info})
        except cx_Oracle.DatabaseError as msg:
            print('sql_exec_except:')
            error, = msg.args
            # 获取sql执行失败时的错误信息  #err cx_oracle 返回的报错
            err_msg = error.message
            respone = 'sql执行异常,报错信息为(sql_exec_except):  ' + err_msg
            return HttpResponse(respone)
        except Exception as err:
            print(err)
            return HttpResponse(err)



def oratk_most_get_ash_config_ajax(request):
    if request.method == 'POST':
        ##获取要查询的数据库连接串
        select_conn_string = request.POST.get('ash_conn_string', None)
        dbname = select_conn_string.split('=')[0]
        conn_string = select_conn_string.split('=')[1]
        ##获取判断是否为rac的sqltext
        sqltext_rac = cron_models.ora_point_info.objects.filter(name='ins_rac_flag').values('sqltext').first()
        sqltext_rac = ast.literal_eval(sqltext_rac['sqltext'])[0][1]
        # ast.literal_eval
        # 如果有分号去掉分号
        if sqltext_rac.rstrip()[-1] == ';':
            sqltext_rac = sqltext_rac.rstrip()[:-1]

        ##获取ash_min_time_config的sqltext
        sqltext_ash_mintime_val = cron_models.ora_point_info.objects.filter(name='ash_min_time').values('sqltext').first()
        sqltext_ash_mintime_val_eval = ast.literal_eval(sqltext_ash_mintime_val['sqltext'].replace('\r', '').replace('\n', ''))
        sqltext_ash_mintime = sqltext_ash_mintime_val_eval[0][1]
        # ast.literal_eval
        # 如果有分号去掉分号
        if sqltext_ash_mintime.rstrip()[-1] == ';':
            sqltext_ash_mintime = sqltext_ash_mintime.rstrip()[:-1]
        try:
            ##数据库连接
            db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
            cursor = db.cursor()
            cursor_ash_mintime = db.cursor()
            ##执行sql
            cursor.execute(sqltext_rac)
            ##列名
            columns = [col[0] for col in cursor.description]
            ##获取结果
            result = cursor.fetchall()
            result_rac = result[0][0]

            ##获取ash_min_time
            cursor_ash_mintime.execute(sqltext_ash_mintime)
            ##列名
            columns_snapshot = [col[0] for col in cursor_ash_mintime.description]
            ##获取结果
            result_ash_min_time = cursor_ash_mintime.fetchall()
            result_ash_min_time = result_ash_min_time[0][0]

            result_ash_mintime_info = '''ash_info: v$active_session_history的最小时间为 %s'''%(result_ash_min_time)
            if result_rac == 'TRUE':
                instance_type = 'RAC'
            elif result_rac == 'FALSE':
                instance_type = 'SINGLE'
            print(result_ash_mintime_info,instance_type)
            ash_result = {}
            ash_result['result_ash_mintime_info'] = result_ash_mintime_info
            ash_result['instance_type'] = instance_type
            ash_result['code_status'] = 'SUCCESS'
            return JsonResponse(ash_result)
        except Exception as err:
            ash_result = {}
            ash_result['code_status'] = 'ERROR'
            ash_result['error_info'] = err
            return JsonResponse(ash_result)


def oratk_most_awr_ash_report_ajax(request):
    if request.method == 'POST':
        try:
            ##awr&ash历史记录tab
            currentpage_num = int(request.POST.get('page', '1'))
            line_count_set = int(request.POST.get('line_count', 5))
            user_search = request.POST.get('user_search', 'all')
            url = 'oratk_most_awr_ash_report_ajax'

            awr_report_tag = request.POST.get('awr_report_tag', None)
            ash_report_tag = request.POST.get('ash_report_tag', None)
            if awr_report_tag == 'YES' or ash_report_tag == 'YES':
                ##数据库信息
                select_conn_string = request.POST.get('conn_string', None)
                dbname = select_conn_string.split('=')[0]
                conn_string = select_conn_string.split('=')[1]
                ##数据库连接
                db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
                ##获得查询dbid信息的sqltext
                dbid_info = cron_models.ora_point_info.objects.filter(name='dbid_info').values('sqltext').first()
                dbid_info_sqltext = ast.literal_eval(dbid_info['sqltext'].replace('\r', '').replace('\n', ''))[0][1]
                ##获取dbid inst_id
                cursor_dbinfo = db.cursor()
                cursor_dbinfo.execute(dbid_info_sqltext)
                result_dbinfo = cursor_dbinfo.fetchall()
                dbid = result_dbinfo[0][0]
                ##获取报告类型
                report_content_type = request.POST.get('report_content_type', None)
                ##获取是否为rac
                rac_mode_status_value = request.POST.get('rac_mode_status_value', None)
            ##生成awr报告
            if awr_report_tag == 'YES':
                snapshot_val_list = request.POST.get('snapshot_val_list', None)
                ##报告类型
                report_type = 'awr'
                ##报告格式
                report_format = report_content_type
                ##数据库连接
                cursor_awr_report = db.cursor()
                ##获取start_snapshot_id  end_snapshot_id
                snapshot_val_list = ast.literal_eval(snapshot_val_list)
                start_snapshot_id = snapshot_val_list[0].split('_')[2]
                end_snapshot_id = snapshot_val_list[1].split('_')[2]
                ##snapshot范围
                snapshot_range = start_snapshot_id + ' -- ' + end_snapshot_id
                ##snapshot日期范围
                start_time = snapshot_val_list[0].split('_')[0] + ' ' + snapshot_val_list[0].split('_')[1]
                end_time = snapshot_val_list[1].split('_')[0] + ' ' + snapshot_val_list[1].split('_')[1]
                date_range = start_time + ' -- ' + end_time
                ##awr报告
                dbid = result_dbinfo[0][0]
                ##
                ii = 0
                for i in result_dbinfo:
                    inst_id = i[1]
                    inst_type = 'SINGLE'
                    if rac_mode_status_value == '1':
                        inst_type = 'RAC_rac' + str(i[1])
                    awr_report_sql = '''SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_%s(%s,%s,%s,%s))
                                                            ''' % (report_format,dbid, inst_id, start_snapshot_id, end_snapshot_id)
                    print(awr_report_sql)
                    ##生成awr
                    cursor_awr_report.execute(awr_report_sql)
                    result_awr_report = cursor_awr_report.fetchall()
                    ##格式化查询结果，将多行转化成一行,结果中有None对象
                    if  report_format == 'html':
                        ##过滤None对象
                        result_awr_report_format = ''.join(str(col) for row in result_awr_report for col in row if col is not None)
                        #将None对象处理成&nbsp
                        #result_awr_report_format = ''.join(['&nbsp' if col is None else str(col) for row in result_awr_report for col in row])
                    if report_format == 'text':
                        ##每行使用'\n' 拼接
                        result_awr_report_format = '\n'.join(['' if col is None else str(col) for row in result_awr_report for col in row])
                    if ii > 0:
                        conn_string = ''
                    cron_models.oratk_awr_ash_report.objects.create(dbinfo=dbname, conn_str=conn_string,
                                                                    report_type=report_type,
                                                                    report_format=report_format,
                                                                    snapshot_range=snapshot_range,
                                                                    date_range=date_range,
                                                                    report_content=result_awr_report_format,
                                                                    inst_type=inst_type)

                    ii += 1

                    if rac_mode_status_value == '0':
                        break

                ##awr_global_report_html
                if rac_mode_status_value == '1':
                    awr_global_report_sql = '''SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_global_report_%s(%s,CAST(null AS VARCHAR2(10)),%s,%s))
                                                                                    ''' % (report_format,dbid, start_snapshot_id, end_snapshot_id)
                    inst_type = 'RAC_global'
                    conn_string = ''
                    cursor_awr_report.execute(awr_global_report_sql)
                    result_awr_report = cursor_awr_report.fetchall()
                    ##格式化查询结果，将多行转化成一行
                    result_awr_report_format = ''.join(
                        str(col) for row in result_awr_report for col in row if col is not None)
                    cron_models.oratk_awr_ash_report.objects.create(dbinfo=dbname, conn_str=conn_string,
                                                                    report_type=report_type,
                                                                    report_format=report_format,
                                                                    snapshot_range=snapshot_range,
                                                                    date_range=date_range,
                                                                    report_content=result_awr_report_format,
                                                                    inst_type=inst_type)
            ##生成ash报告
            if ash_report_tag == 'YES':
                run_date_range = request.POST.get('run_date_range', None)
                ##ash 开始结束时间
                start_time = run_date_range.split('/')[0].strip()
                end_time = run_date_range.split('/')[1].strip()
                date_range = start_time[2:].replace('-','/') + ' -- ' + end_time[2:].replace('-','/')
                ##报告类型
                report_type = 'ash'
                ##实例类型
                inst_type =  'SINGLE'
                ##报告格式
                report_format = report_content_type
                ##数据库连接
                cursor_ash_report = db.cursor()
                # #inst_id
                # inst_id = result_dbinfo[0][1]
                #ash报告

                ii = 0
                for i in result_dbinfo:
                    inst_id = i[1]
                    inst_type = 'SINGLE'
                    if rac_mode_status_value == '1':
                        inst_type = 'RAC_rac' + str(i[1])
                    ash_report_sql = '''SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ash_report_%s(%s,%s,to_date('%s','yyyy/mm/dd hh24:mi:ss'),to_date('%s','yyyy/mm/dd hh24:mi:ss')))
                                                                                                ''' % (
                    report_format, dbid, inst_id, start_time, end_time)
                    print(ash_report_sql)
                    ##生成ash
                    cursor_ash_report.execute(ash_report_sql)
                    result_ash_report = cursor_ash_report.fetchall()
                    ##格式化查询结果，将多行转化成一行,结果中有None对象
                    if  report_format == 'html':
                        ##过滤None对象
                        result_ash_report_format = ''.join(str(col) for row in result_ash_report for col in row if col is not None)
                        #将None对象处理成&nbsp
                        #result_awr_report_format = ''.join(['&nbsp' if col is None else str(col) for row in result_awr_report for col in row])
                    if report_format == 'text':
                        ##每行使用'\n' 拼接
                        result_ash_report_format = '\n'.join(['' if col is None else str(col) for row in result_ash_report for col in row])
                    if ii > 0:
                        conn_string = ''
                    cron_models.oratk_awr_ash_report.objects.create(dbinfo=dbname, conn_str=conn_string,
                                                                    report_type=report_type,
                                                                    report_format=report_format,
                                                                    date_range=date_range,
                                                                    report_content=result_ash_report_format,
                                                                    inst_type=inst_type)

                    ii += 1

                    if rac_mode_status_value == '0':
                        break
            ##删除数据
            delete_tag = request.POST.get('delete_tag', None)
            if delete_tag == 'YES':
                nid = request.POST.get('nid', None)
                cron_models.oratk_awr_ash_report.objects.filter(id=nid).delete()

            ##返回awr&ash 历史记录tab
            if user_search != 'all':
                tab_awr_ash_report_result = cron_models.oratk_awr_ash_report.objects.filter(Q(dbinfo__contains=user_search)
                                                                                            | Q(conn_str__contains=user_search)
                                                                                            | Q(report_type__contains=user_search)
                                                                                            | Q(report_format__contains=user_search)
                                                                                            | Q(snapshot_range__contains=user_search)
                                                                                            | Q(date_range__contains=user_search)
                                                                                            | Q(inst_type__contains=user_search)).order_by('-crtime')
            else:
                user_search = ''
                tab_awr_ash_report_result = cron_models.oratk_awr_ash_report.objects.all().order_by('-crtime')
            v_paginator(tab_awr_ash_report_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_tab_most_awr_ash_report.html',
                          {'url': url, 'current_page': current_page, 'paginator': paginator,
                           'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list, 'search': user_search})
        except cx_Oracle.DatabaseError as msg:
            print('sql_exec_except:')
            error, = msg.args
            # 获取sql执行失败时的错误信息  #err cx_oracle 返回的报错
            err_msg = error.message
            respone = 'ERR:sql执行异常,报错信息为(sql_exec_except):  ' + err_msg
            return HttpResponse(respone)
        except Exception as err:
            return HttpResponse('ERR:',err)

def oratk_most_get_awr_ash_report_ajax(request):
    if request.method == 'GET':
        id = request.GET.get('nid', None)
        report_content = cron_models.oratk_awr_ash_report.objects.filter(id=id).first()
        return render(request, 'oratk_app/oratk_most_get_awr_ash_report.html', {'report_content': report_content})




#下载
def oratk_most_awr_ash_report_download(request):
    if request.method == 'GET':
        id = request.GET.get('nid', None)
        report_content = cron_models.oratk_awr_ash_report.objects.filter(id=id).values('report_content','conn_str','report_type','report_format','inst_type').first()
        ##文件名字拼装
        file_name_part1 = report_content['conn_str']
        file_name_part1 = file_name_part1.split(':')[0].split('.',2)[2]
        file_name_part2 = report_content['report_type']+'_'+report_content['report_format']
        file_name_part3 = report_content['inst_type']
        file_name_part4 = time.strftime('%m%d%H%M%S', time.localtime(time.time()))
        file_name_part5 = report_content['report_format']
        file_name = file_name_part1 + '_' +file_name_part2 + '_' +file_name_part3 + '_' + file_name_part4 + '.'+file_name_part5

        print(report_content['report_content'])
        response = HttpResponse(report_content['report_content'], content_type='text/plain')
        response['Content-Disposition'] = 'attachment; filename=%s'%(file_name)
        return response
#####################巡检#################


#####################巡检#################
# def monitor(request):
#     if request.method == 'POST':
#         pass
#     if request.method == 'GET':
#         if request.session.get('is_login', None):
#             ##关闭浏览器删除session
#             request.session.set_expiry(0)
#             return render(request, 'oratk_app/monitor.html')
#         else:
#             return redirect('/basic_app/login.html')

#
# def command_exec_val(*args):
#     val1 = args[0]
#     val2 = args[1]
#     if len(val2) == 0:
#         return

##即时巡检
def oratk_monitor_immediate(request):
    if request.method == 'POST':
        curr_time2 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        ##获取要查询的数据库连接串
        # select_conn_string.split()[1] 为对应的连接串
        select_conn_string = request.POST.get('conn_string', None)
        dbname = select_conn_string.split('=')[0]
        conn_string = select_conn_string.split('=')[1]
        ip = re.findall(r"\b(?:[0-9]{1,3}\.){3}[0-9]{1,3}\b", conn_string)
        ip = ip[0]
        batch_id = time.strftime('%Y%m%d%H%M%S', time.localtime(time.time()))
        cron_models.oratk_monitor_immediate.objects.create(batch_id=batch_id, start_time=curr_time2, db_name=dbname,
                                                           db_conn=conn_string, status='running',
                                                           end_time='', sub_time='', remark='正在执行')
        ##下面为执行主机部分巡检
        ##ssh执行命令
        try:
            private_key = paramiko.RSAKey.from_private_key_file(
                '/Users/liwk/py/oratk_web/templates/public_key/dubai_oddb_key')
            ##ssh连接
            client = paramiko.SSHClient()
            client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            ##连接服务器
            if ip == '54.155.11.108' or ip == '54.220.137.84':
                print(111)
                client.connect(hostname=ip, port=22, username='ec2-user', pkey=private_key)
            else:
                client.connect(hostname=ip, port=22, username='dbmonitor', password='dbmonitor')
            ####拼装命令
            ##os信息
            # os版本
            os_version = 'cat /etc/issue | head -1'

            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(os_version))
            os_version_val = str(stdout.read(), 'utf-8')
            os_version_err = str(stderr.read(), 'utf-8')
            if len(os_version_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='os_version',
                                                                  err_info=os_version_err,
                                                                  command_name=os_version)

            # os位数
            os_bit = 'getconf LONG_BIT'
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(os_bit))
            os_bit_val = str(stdout.read(), 'utf-8')
            os_bit_err = str(stderr.read(), 'utf-8')
            if len(os_bit_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='os_bit',
                                                                  err_info=os_bit_err,
                                                                  command_name=os_bit)

            # 是否为虚拟机
            computer_type = 'cat /proc/scsi/scsi | wc -l '
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(computer_type))
            computer_type_val = str(stdout.read(), 'utf-8')
            if int(computer_type_val) > 1:
                computer_type_val = '物理机'
            else:
                computer_type_val = '虚拟机'
            computer_type_err = str(stderr.read(), 'utf-8')
            if len(computer_type_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='computer_type',
                                                                  err_info=computer_type_err,
                                                                  command_name=computer_type)

            ##os_version巡检结果保存表中
            cron_models.oratk_monitor_info_os.objects.create(batch_id=batch_id, monitor_time=curr_time2,
                                                             os_name=os_version_val, os_bit=os_bit_val,
                                                             computer_type=computer_type_val)

            ####cpu巡检
            # cpu个数
            cpu_count = "cat /proc/cpuinfo | grep 'physical id' | sort | uniq | wc -l"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(cpu_count))
            cpu_count_val = str(stdout.read(), 'utf-8')
            cpu_count_err = str(stderr.read(), 'utf-8')
            if len(cpu_count_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='cpu_count',
                                                                  err_info=cpu_count_err,
                                                                  command_name=cpu_count)

            # cpu物理核心数
            cpu_core_physics = "cat /proc/cpuinfo |grep 'physical id' | sort -u |wc -l"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(cpu_core_physics))
            cpu_core_physics_val = str(stdout.read(), 'utf-8')
            cpu_core_physics_err = str(stderr.read(), 'utf-8')
            if len(cpu_core_physics_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='cpu_core_physics',
                                                                  err_info=cpu_core_physics_err,
                                                                  command_name=cpu_core_physics)

            # cpu逻辑核心
            cpu_core_logic = "cat /proc/cpuinfo |grep 'processor'  |wc -l"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(cpu_core_logic))
            cpu_core_logic_val = str(stdout.read(), 'utf-8')
            cpu_core_logic_err = str(stderr.read(), 'utf-8')
            if len(cpu_core_logic_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='cpu_core_logic',
                                                                  err_info=cpu_core_logic_err,
                                                                  command_name=cpu_core_logic)

            # cpu使用率
            cpu_rate = "top -n 1 -b | awk -F ' ' 'NR==3 {print $2}' | awk -F '%' '{print $1}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(cpu_rate))
            cpu_rate_val = str(stdout.read(), 'utf-8').replace('\n', '')
            # cpu_rate_val = round((int(float(cpu_rate_val)) * 100),2)
            cpu_rate_err = str(stderr.read(), 'utf-8')
            if len(cpu_rate_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='cpu_rate',
                                                                  err_info=cpu_rate_err,
                                                                  command_name=cpu_rate)
            # cpu 1分钟load
            # cpu_load_1 = "top -n 1 -b | awk -F ' ' 'NR==1 {print $12}' |  awk -F ',' '{print $1}'"
            cpu_load_1 = " top -n 1 -b | awk -F 'average: ' 'NR==1 {print $2}' | awk -F ', ' '{print $1}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(cpu_load_1))
            cpu_load_1_val = str(stdout.read(), 'utf-8')
            cpu_load_1_err = str(stderr.read(), 'utf-8')
            if len(cpu_load_1_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='cpu_load_1',
                                                                  err_info=cpu_load_1_err,
                                                                  command_name=cpu_load_1)
            # cpu 5分钟load
            # cpu_load_5 = "top -n 1 -b | awk -F ' ' 'NR==1 {print $13}' |  awk -F ',' '{print $1}'"
            cpu_load_5 = "top -n 1 -b | awk -F 'average: ' 'NR==1 {print $2}' | awk -F ', ' '{print $2}'"

            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(cpu_load_5))
            cpu_load_5_val = str(stdout.read(), 'utf-8')
            cpu_load_5_err = str(stderr.read(), 'utf-8')
            if len(cpu_load_5_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='cpu_load_5',
                                                                  err_info=cpu_load_5_err,
                                                                  command_name=cpu_load_5)

            ##将cpu信息保存到表中
            print('insert_cpu')
            cron_models.oratk_monitor_info_cpu.objects.create(batch_id=batch_id, monitor_time=curr_time2,
                                                              cpu_count=cpu_count_val,
                                                              cpu_core_physics=cpu_core_physics_val,
                                                              cpu_core_logic=cpu_core_logic_val,
                                                              use_rate_perc=cpu_rate_val,
                                                              load_1=cpu_load_1_val, load_5=cpu_load_5_val)

            ####mem巡检
            # mem_free = "cat /proc/meminfo | grep -E '^MemFree|^Buffers|^Cached|'  | awk '{ sum += $2 } END { print sum }'"
            mem_info = "cat /proc/meminfo | grep -E '^MemTotal|^MemFree|^Buffers|^Cached'  | awk '{print $2}' |xargs echo"

            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(mem_info))
            mem_info_val = str(stdout.read(), 'utf-8')
            mem_info_err = str(stderr.read(), 'utf-8')
            if len(mem_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='mem_info',
                                                                  err_info=mem_info_err,
                                                                  command_name=mem_info)
            # 内存总数
            mem_total = int(mem_info_val.split()[0])
            # free
            mem_free = int(mem_info_val.split()[1]) + int(mem_info_val.split()[2]) + int(mem_info_val.split()[3])
            # 内存使用率
            mem_rate = round((((mem_total - mem_free) / mem_total) * 100), 2)
            # swap内存信息
            swap_info = "cat /proc/meminfo | grep -E '^SwapTotal|^SwapFree' | awk '{print $2}' | xargs echo"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(swap_info))
            swap_info_val = str(stdout.read(), 'utf-8')
            swap_info_err = str(stderr.read(), 'utf-8')
            if len(swap_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='swap_info',
                                                                  err_info=swap_info_err,
                                                                  command_name=swap_info)
            swap_total = int(swap_info_val.split()[0])
            swap_free = int(swap_info_val.split()[1])
            # swap使用率
            if swap_total == 0:
                swap_rate = 0
            else:
                swap_rate = round((((swap_total - swap_free) / swap_total) * 100), 2)
            # 大页信息，hugepage
            hugepage_info = "cat /proc/meminfo | grep -E '^HugePages_Total|^HugePages_Free|^Hugepagesize'| awk '{print $2}' | xargs echo"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(hugepage_info))
            hugepage_info_val = str(stdout.read(), 'utf-8')
            hugepage_info_err = str(stderr.read(), 'utf-8')
            if len(hugepage_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='hugepage_info',
                                                                  err_info=hugepage_info_err,
                                                                  command_name=hugepage_info)
            hugepage_total = hugepage_info_val.split()[0]
            hugepage_free = hugepage_info_val.split()[1]
            hugepagesize = hugepage_info_val.split()[2]
            print('mem_info')
            cron_models.oratk_monitor_info_mem.objects.create(batch_id=batch_id, monitor_time=curr_time2,
                                                              mem_total_kb=mem_total,
                                                              mem_use_rate_perc=mem_rate,
                                                              swap_total_kb=swap_total,
                                                              swap_use_rate_perc=swap_rate,
                                                              hugepage_total_kb=hugepage_total,
                                                              hugepage_free_kb=hugepage_free,
                                                              hugepagesize_kb=hugepagesize)

            ####disk巡检
            # disk空间信息
            disk_space_info = "df -h  -l  | sed  '1d;/ /!N;s/\\n//;s/ \+/ /;' |  awk '{print $6,$2,$5}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(disk_space_info))
            disk_space_info_val = stdout.readlines()
            # print(disk_space_info_val)
            disk_space_info_err = str(stderr.read(), 'utf-8')
            if len(disk_space_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='disk_space_info',
                                                                  err_info=disk_space_info_err,
                                                                  command_name=disk_space_info)
            print('disk_space_info')
            for res in disk_space_info_val:
                dir_path = res.split()[0]
                dir_space = res.split()[1]
                space_use_rate = res.split()[2].split('%')[0]
                cron_models.oratk_monitor_info_disk.objects.create(batch_id=batch_id, monitor_time=curr_time2,
                                                                   dir_path=dir_path,
                                                                   dir_space_g=dir_space,
                                                                   space_use_rate_perc=space_use_rate)

            # disk inode信息
            disk_inode_info = "df -i  -l  | sed  '1d;/ /!N;s/\\n//;s/ \+/ /;' |  awk '{print $6,$2,$5}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(disk_inode_info))
            disk_inode_info_val = stdout.readlines()
            disk_inode_info_err = str(stderr.read(), 'utf-8')
            if len(disk_inode_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='disk_inode_info',
                                                                  err_info=disk_inode_info_err,
                                                                  command_name=disk_inode_info)
            for res in disk_inode_info_val:
                # print(res)
                dir_path = res.split()[0]
                inode_count = res.split()[1]
                inode_use_rate = res.split()[2].split('%')[0]
                ##根据目录和批次把inode信息，update到表中
                id_val = cron_models.oratk_monitor_info_disk.objects.filter(dir_path=dir_path,
                                                                            batch_id=batch_id).first()
                cron_models.oratk_monitor_info_disk.objects.filter(id=id_val.id).update(inode_count=inode_count,
                                                                                        inode_use_rate_perc=inode_use_rate)

            # disk io信息
            disk_io_info = "iostat -x 1 1 -m | sed '1,6d'|sed '/^$/d' | awk '{print $1,$4,$5,$6,$7,$10,$11,$12}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(disk_io_info))
            disk_io_info_val = stdout.readlines()
            disk_io_info_err = str(stderr.read(), 'utf-8')
            if len(disk_io_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='disk_io_info',
                                                                  err_info=disk_io_info_err,
                                                                  command_name=disk_io_info)
            print('disk_io_info')
            for res in disk_io_info_val:
                disk_name = res.split()[0]
                r_exec_count = res.split()[1]
                w_exec_count = res.split()[2]
                r_space = res.split()[3]
                w_space = res.split()[4]
                io_await = res.split()[5]
                io_svctm = res.split()[6]
                io_util = res.split()[7]
                cron_models.oratk_monitor_info_disk_io.objects.create(batch_id=batch_id, monitor_time=curr_time2,
                                                                      disk_name=disk_name,
                                                                      r_exec_count=r_exec_count,
                                                                      w_exec_count=w_exec_count,
                                                                      r_space_mb_s=r_space, w_space_mb_s=w_space,
                                                                      io_await_ms=io_await,
                                                                      io_svctm_ms=io_svctm, io_util_perc=io_util)
            # network 信息
            network_io_info = "sar -n DEV 1 1 | grep Average | sed '1d' | awk '{print $2,$5,$6}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(network_io_info))
            network_io_info_val = stdout.readlines()
            network_io_info_err = str(stderr.read(), 'utf-8')
            if len(network_io_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='network_io_info',
                                                                  err_info=network_io_info_err,
                                                                  command_name=network_io_info)
            for res in network_io_info_val:
                interface_name = res.split()[0]
                up_io = res.split()[1]
                down_io = res.split()[2]
                cron_models.oratk_monitor_info_network.objects.create(batch_id=batch_id, monitor_time=curr_time2,
                                                                      interface_name=interface_name,
                                                                      up_kbyte_s=up_io,
                                                                      down_kbyte_s=down_io)

            ##将此批次主机的结果保存到
            os_version_info = cron_models.oratk_monitor_info_os.objects.values('os_name', 'os_bit',
                                                                               'computer_type').filter(
                batch_id=batch_id)
            os_version_info = list(os_version_info)
            cpu_info = cron_models.oratk_monitor_info_cpu.objects.values('cpu_count', 'cpu_core_physics',
                                                                         'cpu_core_logic', 'use_rate_perc', 'load_1',
                                                                         'load_5').filter(batch_id=batch_id)
            cpu_info = list(cpu_info)
            mem_info = cron_models.oratk_monitor_info_mem.objects.values('mem_total_kb', 'mem_use_rate_perc',
                                                                         'swap_total_kb', 'swap_use_rate_perc',
                                                                         'hugepage_total_kb', 'hugepage_free_kb',
                                                                         'hugepagesize_kb').filter(batch_id=batch_id)
            mem_info = list(mem_info)
            disk_space_info = cron_models.oratk_monitor_info_disk.objects.values('dir_path', 'dir_space_g',
                                                                                 'space_use_rate_perc', 'inode_count',
                                                                                 'inode_use_rate_perc').filter(
                batch_id=batch_id)
            disk_space_info = list(disk_space_info)
            disk_io_info = cron_models.oratk_monitor_info_disk_io.objects.values('disk_name', 'r_exec_count',
                                                                                 'w_exec_count', 'r_space_mb_s',
                                                                                 'w_space_mb_s', 'io_await_ms',
                                                                                 'io_svctm_ms', 'io_util_perc').filter(
                batch_id=batch_id)
            disk_io_info = list(disk_io_info)
            network_info = cron_models.oratk_monitor_info_network.objects.values('interface_name', 'up_kbyte_s',
                                                                                 'down_kbyte_s').filter(
                batch_id=batch_id)
            network_info = list(network_info)
            point_info = {'os_version': os_version_info, 'cpu': cpu_info,
                          'memory': mem_info, 'disk': disk_space_info, 'disk_io': disk_io_info,
                          'network': network_info}
            ###将数据结果以字典方式写入表
            i = 0
            for title_info, res_list in point_info.items():
                host_rec = {}
                # 标题
                host_rec['title_info_' + str(i)] = title_info

                # 列名和值
                col = []
                # print(res_list)
                for k, v in res_list[0].items():
                    ##列名
                    col.append(k)
                    host_rec['host_columns' + str(i)] = col
                val = []
                for row_dic in res_list:
                    val_row = []
                    for k, v in row_dic.items():
                        ##列名
                        val_row.append(v)
                    val.append(val_row)
                host_rec['val' + str(i)] = val
                cron_models.oratk_monitor_host_record.objects.create(batch_id=batch_id, monitor_name=title_info,
                                                                     monitor_record=host_rec)
                i = i + 1
        ##ssh异常处理
        except Exception as e:
            res_err = e
            print('Exception: %s' % res_err)
            cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='SSH_ERR',
                                                              err_info=res_err)
        ##以上为主机部分巡检

        ##添加数据库异常信息
        try:
            ##

            ##下面执行为数据库部分巡检
            db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
            cursor = db.cursor()

            ##定义数据库巡检项
            db_monitor_point = ['cur_date', 'dbinfo', 'sga_info', 'pga_info', 'lock', 'event_overview',
                                'event_stats', 'tablespace', 'temp_tablespace', 'redo_switch_2d', 'transaction',
                                'cursor_share']
            # , 'sga_info', 'pga_info','lock','event_overview','event_stats','tablespace','temp_tablespace',
            #                     'redo_switch_2d','transaction','job','cursor_share']
            sqllist = []
            for point in db_monitor_point:
                # print(point)
                sql = cron_models.ora_point_info.objects.filter(name=point).first()
                # print(sql.sqltext)
                sql = ast.literal_eval(sql.sqltext.replace('\r', '').replace('\n', ''))
                sqllist = sqllist + sql
            # sqllist = sql_date + sql_lock + sql_session_detail_lock + sql_event
            sql_exec_c = 0
            for i in sqllist:
                sql_res = {}
                sql_res['desc' + str(sql_exec_c)] = i[0] + ':'
                # 获取sql文本
                sqltext = i[1]
                cursor.execute(sqltext)
                ##列名
                sql_res['columns' + str(sql_exec_c)] = [col[0] for col in cursor.description]
                ##获取结果
                # result = cursor.fetchall()
                sql_res['result' + str(sql_exec_c)] = cursor.fetchall()

                sql_exec_c = sql_exec_c + 1
                cron_models.oratk_monitor_sql_record.objects.create(batch_id=batch_id, monitor_name=i[0],
                                                                    monitor_record=sql_res)
        except Exception as e:
            res_err = e
            print('Exception: %s' % res_err)
            cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='DB_ERR',
                                                              err_info=res_err)

            ##以上为数据库部分巡检

        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)

            ##计算耗时
            start_time = cron_models.oratk_monitor_immediate.objects.filter(batch_id=batch_id).first()
            end_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            seconds = (parse(end_time) - parse(start_time.start_time)).seconds

            ##如果巡检存在错误
            err_info = cron_models.oratk_monitor_info_err.objects.values('batch_id', 'monitor_name', 'err_info').filter(
                batch_id=batch_id)
            if len(list(err_info)) > 0:
                cron_models.oratk_monitor_immediate.objects.filter(batch_id=batch_id).update(status='error',
                                                                                             remark='巡检报错',
                                                                                             sub_time=seconds)
            else:
                cron_models.oratk_monitor_immediate.objects.filter(batch_id=batch_id).update(status='success',
                                                                                             remark='执行成功',
                                                                                             sub_time=seconds)
            return redirect('/oratk_app/oratk_monitor_immediate')
        else:
            return redirect('/basic_app/login.html')

    if request.method == 'GET':
        record_result = cron_models.oratk_monitor_immediate.objects.all().order_by('-start_time')
        instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='LOCKED')).order_by('dbname')
        currentpage_num = int(request.GET.get('page', '1'))
        line_count_set = int(request.GET.get('line_count', 5))
        user_search = request.GET.get('user_search', 'all')
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            v_paginator(record_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_monitor_immediate.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search, 'instanceinfo_result': instanceinfo_result})
        else:
            return redirect('/basic_app/login.html')


##巡检详细报告
def oratk_monitor_immediate_report_info(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            batch_id = request.GET.get('batch_id')
            db_info = cron_models.oratk_monitor_immediate.objects.filter(batch_id=batch_id).first()
            ##判断巡检中是否有错误
            err_info = cron_models.oratk_monitor_info_err.objects.values('batch_id', 'monitor_name', 'err_info').filter(
                batch_id=batch_id)
            if len(list(err_info)) > 0:
                return render(request, 'oratk_app/oratk_monitor_immediate_report_info_err.html',
                              {'err_info': list(err_info), 'db_info': db_info})
            else:
                # os_version_info = cron_models.oratk_monitor_info_os.objects.values('os_name','os_bit','computer_type').filter(batch_id=batch_id)
                # os_version_info = list(os_version_info)
                # cpu_info = cron_models.oratk_monitor_info_cpu.objects.values('cpu_count','cpu_core_physics','cpu_core_logic','use_rate_perc','load_1','load_5').filter(batch_id=batch_id)
                # cpu_info = list(cpu_info)
                # mem_info = cron_models.oratk_monitor_info_mem.objects.values('mem_total_kb','mem_use_rate_perc','swap_total_kb','swap_use_rate_perc','hugepage_total_kb','hugepage_free_kb','hugepagesize_kb').filter(batch_id=batch_id)
                # mem_info = list(mem_info)
                # disk_space_info = cron_models.oratk_monitor_info_disk.objects.values('dir_path','dir_space_g','space_use_rate_perc','inode_count','inode_use_rate_perc').filter(batch_id=batch_id)
                # disk_space_info = list(disk_space_info)
                # disk_io_info = cron_models.oratk_monitor_info_disk_io.objects.values('disk_name','r_exec_count','w_exec_count','r_space_mb_s','w_space_mb_s','io_await_ms','io_svctm_ms','io_util_perc').filter(batch_id=batch_id)
                # disk_io_info = list(disk_io_info)
                # network_info = cron_models.oratk_monitor_info_network.objects.values('interface_name','up_kbyte_s','down_kbyte_s').filter(batch_id=batch_id)
                # network_info = list(network_info)
                # point_info = {'os_version_info':os_version_info,'cpu_info':cpu_info,
                #               'mem_info':mem_info,'disk_space_info':disk_space_info,'disk_io_info':disk_io_info,
                #               'network_info':network_info}

                # ###主机模板
                # res = {'db_info':db_info}
                # i = 0
                # for title_info,res_list in point_info.items():
                #     #标题
                #     res['title_info_' + str(i)] = title_info
                #
                #
                #     #列名和值
                #     col = []
                #     for k,v in res_list[0].items():
                #         ##列名
                #         col.append(k)
                #         res['host_columns' + str(i)] = col
                #     val = []
                #     for row_dic in  res_list:
                #         val_row = []
                #         for k,v in row_dic.items():
                #             ##列名
                #             val_row.append(v)
                #         val.append(val_row)
                #     res['val' + str(i)] = val
                #     i = i + 1
                ##以上是主机数据字典

                res = {'db_info': db_info}

                ##以下是获取主机结果字典
                host_rec = cron_models.oratk_monitor_host_record.objects.filter(batch_id=batch_id)
                host_rec_dic = {}
                for rec_dic in host_rec:
                    host_rec_dic = dict(host_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                res = dict(res, **host_rec_dic)

                ##以下是sql数据字典
                sql_rec = cron_models.oratk_monitor_sql_record.objects.filter(batch_id=batch_id)
                sql_rec_dic = {}
                for rec_dic in sql_rec:
                    sql_rec_dic = dict(sql_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                ##以上是sql数据字典
                res = dict(res, **sql_rec_dic)
                # print(res)
                return render(request, 'oratk_app/oratk_monitor_immediate_report_info.html', res)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')


##巡检报告(report)
def oratk_monitor_immediate_report(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            batch_id = request.GET.get('batch_id')
            db_name = request.GET.get('db_name')
            limit_res = cron_models.oratk_monitor_sql_host_limit.objects.all()
            db_info = cron_models.oratk_monitor_immediate.objects.filter(batch_id=batch_id).first()
            ##判断是否生成过report
            is_report = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id)
            ##已有报告
            report_limit_change = []
            if is_report.exists():
                print('报告存在')
                ##比较上次生成报告时的阀值与现在模板里阀值比较有没有变化
                source_exec_limit = cron_models.oratk_monitor_report_exec_sql_limit.objects.filter(batch_id=batch_id)
                for source_exec_limit_row in source_exec_limit:
                    model_limit = cron_models.oratk_monitor_sql_host_limit.objects.filter(
                        monitor_name=source_exec_limit_row.monitor_name,
                        monitor_point=source_exec_limit_row.monitor_point).first()
                    # print(source_exec_limit_row.limit_values)
                    # print(model_limit.limit_values)
                    if source_exec_limit_row.limit_values != model_limit.limit_values:
                        report_limit_change.append('1')
                if len(report_limit_change) == 0:
                    print('阀值信息没变')
                    ##如果有报告且阀值信息没变，就返回之前的报告内容
                    ##以下是sql数据字典
                    res = {'db_info': db_info, 'limit_res': limit_res}
                    time_record = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id).first()
                    res['time_record'] = time_record.monitor_make_time
                    # sql_rec = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id)
                    # sql_rec_dic = {}
                    # sql_rec_row_count = 0
                    # for rec_dic in sql_rec:
                    #     sql_rec_dic = dict(sql_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                    #     sql_rec_dic['limit' + str(sql_rec_row_count)] = '( limit: '+ rec_dic.limit_values + ')'
                    #     sql_rec_row_count = sql_rec_row_count + 1
                    # ##以上是sql数据字典
                    # res = dict(res, **sql_rec_dic)
                    # print(res)
                    ##获取sql的数据字典结果集
                    sql_rec = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id,
                                                                              monitor_type='database')
                    sql_rec_dic = {}
                    # sql_rec_row_count = 0
                    if sql_rec.exists:
                        for rec_dic in sql_rec:
                            # if rec_dic.monitor_type_type == 'database':
                            sql_rec_dic = dict(sql_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                            # 取limit值
                            sql_rec_dic['limit' + rec_dic.seq_record] = '( limit: ' + rec_dic.limit_values + ')'
                    ##获取host类型的报告记录
                    host_res = cron_models.oratk_monitor_report.objects.filter(~Q(host_limit=None),
                                                                               batch_id=batch_id,
                                                                               monitor_type='host')
                    host_rec_dic = {}
                    if host_res.exists():
                        # host_rec_row_count = 0
                        for rec_dic in host_res:
                            host_rec_dic = dict(host_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                            #     # 取limit值
                            host_rec_dic['host_limit_' + rec_dic.seq_record] = rec_dic.host_limit
                            # host_rec_dic['host_limit_' + str(host_rec_row_count)] = rec_dic.host_limit
                            # host_rec_row_count = host_rec_row_count + 1
                    res = dict(res, **sql_rec_dic)
                    res = dict(res, **host_rec_dic)
                    # res_list = {'res_list':
                    #                 [[{'title_info': 'title_info_0', 'host_limit': 'host_limit_0','host_columns': 'host_columns0', 'val': 'val0'}],
                    #                 [{'title_info': 'title_info_1', 'host_limit': 'host_limit_1','host_columns': 'host_columns1', 'val': 'val1'}],
                    #                 [{'title_info': 'title_info_2', 'host_limit': 'host_limit_2','host_columns': 'host_columns2', 'val': 'val2'}],
                    #                 [{'title_info': 'title_info_3', 'host_limit': 'host_limit_3','host_columns': 'host_columns3', 'val': 'val3'}],
                    #                 [{'title_info': 'title_info_4', 'host_limit': 'host_limit_4','host_columns': 'host_columns4', 'val': 'val4'}]]}
                    # res = dict(res, **res_list)
                    print(res)
                    return render(request, 'oratk_app/oratk_monitor_immediate_report.html', res)

            ##生成新报告
            if is_report.count() == 0 or len(report_limit_change) > 0:
                curr_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
                print('报告不存在或阀值发生改变')
                ##如果是阀值发生改变，清空report记录和阀值记录、
                if len(report_limit_change) > 0:
                    cron_models.oratk_monitor_report_exec_sql_limit.objects.filter(batch_id=batch_id).delete()
                    cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id).delete()

                ##为防止 在没有超过阀值的记录时，生成报告取时间字段时出现异常，插入报告时间
                cron_models.oratk_monitor_report.objects.create(batch_id=batch_id, monitor_make_time=curr_time)
                ##获取阀值项
                limit_res = cron_models.oratk_monitor_sql_host_limit.objects.all().order_by('monitor_type')
                ##把当前阀值记录到表
                for limit_info in limit_res:
                    cron_models.oratk_monitor_report_exec_sql_limit.objects.create(batch_id=batch_id, db_name=db_name,
                                                                                   monitor_name=limit_info.monitor_name,
                                                                                   monitor_point=limit_info.monitor_point,
                                                                                   limit_values=limit_info.limit_values,
                                                                                   )
                i = 0
                for limit_row in limit_res:
                    limit_values = limit_row.limit_values
                    report_res = {}
                    ##主机模板
                    if limit_row.monitor_type == 'host':
                        ##根据阀值项获取巡检详细记录
                        ##主机模板
                        print(limit_row.monitor_point)
                        host_res = cron_models.oratk_monitor_host_record.objects.filter(batch_id=batch_id,
                                                                                        monitor_name=limit_row.monitor_name).first()
                        host_res = ast.literal_eval(host_res.monitor_record)
                        report_res['title_info_' + str(i)] = [ii for ii in host_res.values()][0]
                        report_res['host_columns' + str(i)] = [ii for ii in host_res.values()][1]
                        host_record_res = []
                        ##cpu
                        if limit_row.monitor_name == 'cpu':
                            if limit_row.monitor_point == 'load_1':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[4]) > float(limit_values):
                                        host_record_res.append(iii)
                            if limit_row.monitor_point == 'load_5':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[5]) > float(limit_values):
                                        host_record_res.append(iii)
                            if limit_row.monitor_point == 'use_rate_perc':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[3]) > float(limit_values):
                                        host_record_res.append(iii)
                            if host_record_res:
                                report_res['val' + str(i)] = host_record_res
                                cron_models.oratk_monitor_report.objects.create(batch_id=batch_id,
                                                                                monitor_name=limit_row.monitor_name,
                                                                                monitor_point=limit_row.monitor_point,
                                                                                monitor_record=report_res,
                                                                                limit_values=limit_values,
                                                                                monitor_type='host',
                                                                                seq_record=str(i),
                                                                                monitor_make_time=curr_time)
                        ##mem
                        if limit_row.monitor_name == 'memory':
                            if limit_row.monitor_point == 'mem_use_rate_perc':
                                for iii in [ii for ii in host_res.values()][2]:
                                    print(iii[1])
                                    if float(iii[1]) > float(limit_values):
                                        host_record_res.append(iii)
                            if limit_row.monitor_point == 'swap_use_rate_perc':
                                for iii in [ii for ii in host_res.values()][2]:
                                    print(iii[3])
                                    if float(iii[3]) > float(limit_values):
                                        host_record_res.append(iii)
                            if host_record_res:
                                report_res['val' + str(i)] = host_record_res
                                cron_models.oratk_monitor_report.objects.create(batch_id=batch_id,
                                                                                monitor_name=limit_row.monitor_name,
                                                                                monitor_point=limit_row.monitor_point,
                                                                                monitor_record=report_res,
                                                                                limit_values=limit_values,
                                                                                monitor_type='host',
                                                                                seq_record=str(i),
                                                                                monitor_make_time=curr_time)

                        ##disk
                        if limit_row.monitor_name == 'disk':
                            if limit_row.monitor_point == 'space_use_rate_perc':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[2]) > float(limit_values):
                                        host_record_res.append(iii)
                            if limit_row.monitor_point == 'inode_use_rate_perc':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[4]) > float(limit_values):
                                        host_record_res.append(iii)
                            if host_record_res:
                                report_res['val' + str(i)] = host_record_res
                                cron_models.oratk_monitor_report.objects.create(batch_id=batch_id,
                                                                                monitor_name=limit_row.monitor_name,
                                                                                monitor_point=limit_row.monitor_point,
                                                                                monitor_record=report_res,
                                                                                limit_values=limit_values,
                                                                                monitor_type='host',
                                                                                seq_record=str(i),
                                                                                monitor_make_time=curr_time)

                        ##disk_io
                        if limit_row.monitor_name == 'disk_io':
                            if limit_row.monitor_point == 'io_util_perc':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[7]) > float(limit_values):
                                        host_record_res.append(iii)
                            if host_record_res:
                                report_res['val' + str(i)] = host_record_res
                                cron_models.oratk_monitor_report.objects.create(batch_id=batch_id,
                                                                                monitor_name=limit_row.monitor_name,
                                                                                monitor_point=limit_row.monitor_point,
                                                                                monitor_record=report_res,
                                                                                limit_values=limit_values,
                                                                                monitor_type='host',
                                                                                seq_record=str(i),
                                                                                monitor_make_time=curr_time)

                        ##network
                        if limit_row.monitor_name == 'network':
                            if limit_row.monitor_point == 'up_kbyte_s':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[1]) > float(limit_values):
                                        host_record_res.append(iii)
                            if limit_row.monitor_point == 'down_kbyte_s':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[2]) > float(limit_values):
                                        host_record_res.append(iii)
                            if host_record_res:
                                print('将主机记录写入数据库')
                                report_res['val' + str(i)] = host_record_res
                                cron_models.oratk_monitor_report.objects.create(batch_id=batch_id,
                                                                                monitor_name=limit_row.monitor_name,
                                                                                monitor_point=limit_row.monitor_point,
                                                                                monitor_record=report_res,
                                                                                limit_values=limit_values,
                                                                                monitor_type='host',
                                                                                seq_record=str(i),
                                                                                monitor_make_time=curr_time)

                    else:
                        ##数据库模板
                        ##根据阀值项获取巡检详细记录
                        print(limit_row.monitor_point)
                        sql_res = cron_models.oratk_monitor_sql_record.objects.filter(batch_id=batch_id,
                                                                                      monitor_name=limit_row.monitor_point).first()

                        sql_res = ast.literal_eval(sql_res.monitor_record)
                        report_res['desc' + str(i)] = [ii for ii in sql_res.values()][0]
                        report_res['columns' + str(i)] = [ii for ii in sql_res.values()][1]
                        record_res = []
                        ###tablespace_use_rate
                        if limit_row.monitor_point == 'tablespace_use_rate':
                            ##循环每条记录
                            for iii in [ii for ii in sql_res.values()][2]:
                                if float(iii[5].split('%')[0]) > float(limit_values.split('%')[0]):
                                    record_res.append(iii)
                        ###temp_tablespce_use_rate
                        if limit_row.monitor_point == 'temp_tablespce_use_rate':
                            ##循环每条记录
                            for iii in [ii for ii in sql_res.values()][2]:
                                if float(iii[2]) > float(limit_values.split('%')[0]):
                                    record_res.append(iii)

                        report_res['result' + str(i)] = record_res
                        if record_res:
                            cron_models.oratk_monitor_report.objects.create(batch_id=batch_id,
                                                                            monitor_name=limit_row.monitor_name,
                                                                            monitor_point=limit_row.monitor_point,
                                                                            monitor_record=report_res,
                                                                            limit_values=limit_values,
                                                                            monitor_type='database',
                                                                            seq_record=str(i),
                                                                            monitor_make_time=curr_time)

                    i = i + 1

                ##返回结果
                ##以下是sql和host的数据字典结果集
                res = {'db_info': db_info, 'limit_res': limit_res}
                time_record = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id).first()
                # 取报告生成时间
                res['time_record'] = time_record.monitor_make_time

                ##获取sql的数据字典结果集
                sql_rec = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id, monitor_type='database')
                sql_rec_dic = {}
                # sql_rec_row_count = 0
                if sql_rec.exists:
                    for rec_dic in sql_rec:
                        # if rec_dic.monitor_type_type == 'database':
                        sql_rec_dic = dict(sql_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                        print(rec_dic.monitor_record)
                        # 取limit值
                        sql_rec_dic['limit' + rec_dic.seq_record] = '( limit: ' + rec_dic.limit_values + ')'

                        # sql_rec_dic['limit' + str(sql_rec_row_count)] = '( limit: '+ rec_dic.limit_values + ')'
                        # sql_rec_row_count = sql_rec_row_count + 1

                # 获取host的数据字典结果集
                ##如果一条记录有多个阀值的情况，将多条合并成一条，并把阀值信息统计到一起 seq_record
                ##如果一条记录，也将阀值信息放到 seq_record
                join_list = ['cpu', 'memory', 'disk', 'disk_io', 'network']
                for i in join_list:
                    host_rec_i = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id, monitor_type='host',
                                                                                 monitor_name=i)
                    if host_rec_i.exists():
                        limit_list = []
                        for row_i in host_rec_i:
                            limit_list.append(row_i.monitor_point + '_limit: ' + row_i.limit_values)
                        host_limit = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id,
                                                                                     monitor_type='host',
                                                                                     monitor_name=i).first()
                        cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id, monitor_type='host',
                                                                        monitor_name=i,
                                                                        monitor_point=host_limit.monitor_point).update(
                            host_limit=limit_list)

                ##获取host类型的报告记录
                host_res = cron_models.oratk_monitor_report.objects.filter(~Q(host_limit=None), batch_id=batch_id,
                                                                           monitor_type='host')
                host_rec_dic = {}
                if host_res.exists():
                    # host_rec_row_count = 0
                    for rec_dic in host_res:
                        host_rec_dic = dict(host_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                        #     # 取limit值
                        host_rec_dic['host_limit_' + rec_dic.seq_record] = rec_dic.host_limit
                        # host_rec_dic['host_limit_' + str(host_rec_row_count)] = rec_dic.host_limit
                        # host_rec_row_count = host_rec_row_count + 1
                res = dict(res, **sql_rec_dic)
                res = dict(res, **host_rec_dic)
                print(len(res))
                if len(res) > 3:
                    cron_models.oratk_monitor_immediate.objects.filter(batch_id=batch_id).update(remark='该报告存在值')
                else:
                    cron_models.oratk_monitor_immediate.objects.filter(batch_id=batch_id).update(remark='该报告为空')
                return render(request, 'oratk_app/oratk_monitor_immediate_report.html', res)
        else:
            return redirect('/basic_app/login.html')


##每日巡检
def oratk_monitor_day(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')


##每周巡检
def oratk_monitor_week(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')


def oratk_monitor_mgr(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')


##巡检阀值管理
def oratk_monitor_limit_mgr(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            if user_search != 'all':
                limit_res = cron_models.oratk_monitor_sql_host_limit.objects.filter(Q(monitor_name=user_search)
                                                                                    | Q(limit_values=user_search)
                                                                                    | Q(remark=user_search)).order_by(
                    'monitor_type')
                v_paginator(limit_res, currentpage_num, line_count_set)
            else:
                user_search = ''
                limit_res = cron_models.oratk_monitor_sql_host_limit.objects.all().order_by('monitor_type')
                v_paginator(limit_res, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_monitor_limit.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search, 'limit_res': limit_res})
        else:
            return redirect('/basic_app/login.html')


##添加数据库阀值
def oratk_monitor_limit_add(request):
    if request.method == 'POST':
        monitor_name = request.POST.get('monitor_name')
        monitor_point = request.POST.get('monitor_point')
        add_limit = request.POST.get('add_limit')
        add_monitor_type = request.POST.get('add_monitor_type')
        add_remake = request.POST.get('add_remake')
        try:
            cron_models.oratk_monitor_sql_host_limit.objects.create(monitor_name=monitor_name,
                                                                    monitor_point=monitor_point, limit_values=add_limit,
                                                                    monitor_type=add_monitor_type, remark=add_remake)
            return redirect('/oratk_app/oratk_monitor_limit_mgr')
        except Exception as e:
            return HttpResponse("添加失败，报错信息: %s" % (e))
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            point_name = cron_models.ora_point_info.objects.all().order_by('name')
            return render(request, 'oratk_app/oratk_monitor_limit_add.html', {'point_name_res': point_name})
        else:
            return redirect('/basic_app/login.html')


# 添加主机阀值
def oratk_monitor_limit_add_host(request):
    if request.method == 'POST':
        monitor_name = request.POST.get('monitor_name')
        monitor_point = request.POST.get('monitor_point')
        add_limit = request.POST.get('add_limit')
        add_monitor_type = request.POST.get('add_monitor_type')
        add_remake = request.POST.get('add_remake')
        try:
            cron_models.oratk_monitor_sql_host_limit.objects.create(monitor_name=monitor_name,
                                                                    monitor_point=monitor_point,
                                                                    monitor_type=add_monitor_type,
                                                                    limit_values=add_limit, remark=add_remake)
            return redirect('/oratk_app/oratk_monitor_limit_mgr')
        except Exception as e:
            return HttpResponse("添加失败，报错信息: %s" % (e))
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            global host_limit_dic
            host_limit_dic = {'cpu': ['use_rate_perc', 'load_1', 'load_5'],
                              'memory': ['mem_use_rate_perc', 'swap_use_rate_perc'],
                              'disk': ['space_use_rate_perc', 'inode_use_rate_perc'], 'disk_io': ['io_util_perc'],
                              'network': ['up_kbyte_s', 'down_kbyte_s']}
            key_list = []
            for key in host_limit_dic.keys():
                key_list.append(key)

            return render(request, 'oratk_app/oratk_monitor_limit_add_host.html', {'host_key_list': key_list})
        else:
            return redirect('/basic_app/login.html')


def oratk_monitor_limit_add_ajax(request):
    if request.method == 'POST':
        point_list = []
        monitor_name = request.POST.get('monitor_name')
        monitor_point_res = cron_models.ora_point_info.objects.filter(name=monitor_name).first()
        for point in ast.literal_eval(monitor_point_res.sqltext.replace('\r', '').replace('\n', '')):
            point_list.append(point[0])
        return render(request, 'oratk_app/oratk_monitor_limit_add_ajax.html', {'point_name': point_list})


def oratk_monitor_limit_add_host_ajax(request):
    if request.method == 'POST':
        monitor_name = request.POST.get('monitor_name')
        point_list = host_limit_dic[monitor_name]
        return render(request, 'oratk_app/oratk_monitor_limit_add_ajax.html', {'point_name': point_list})


##删除阀值信息
def oratk_monitor_limit_delete(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            cron_models.oratk_monitor_sql_host_limit.objects.filter(id=nid).delete()
            return redirect('/oratk_app/oratk_monitor_limit_mgr')
        else:
            return redirect('/basic_app/login.html')


##编辑阀值信息
def oratk_monitor_limit_edit(request):
    if request.method == 'POST':
        v_id = request.POST.get('id')
        add_limit = request.POST.get('add_limit')
        add_remake = request.POST.get('add_remake', '')
        cron_models.oratk_monitor_sql_host_limit.objects.filter(id=v_id).update(limit_values=add_limit,
                                                                                remark=add_remake)
        return redirect('/oratk_app/oratk_monitor_limit_mgr')
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            print(nid)
            edit_res = cron_models.oratk_monitor_sql_host_limit.objects.filter(id=nid).first()
            return render(request, 'oratk_app/oratk_monitor_limit_edit.html', {'edit_res': edit_res})
        else:
            return redirect('/basic_app/login.html')


#############以下 计划任务页-监控项管理 #############
def cron_point_mgr(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            print(user_search)
            ##当用户搜索时：
            if user_search != 'all':
                cron_point_info_result = cron_models.oratk_cron_point_info.objects.filter(Q(name__contains=user_search)
                                                                                          | Q(
                    desc__contains=user_search)
                                                                                          | Q(
                    sqltext__contains=user_search)
                                                                                          | Q(
                    limit__contains=user_search)
                                                                                          | Q(
                    status__contains=user_search)
                                                                                          | Q(
                    point_type__contains=user_search)
                                                                                          | Q(
                    remake__contains=user_search))
                v_paginator(cron_point_info_result, currentpage_num, line_count_set)

            # 当用户没有搜索时：
            else:
                user_search = ''
                cron_point_info_result = cron_models.oratk_cron_point_info.objects.all()
                v_paginator(cron_point_info_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/cron_point_mgr.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')


def cron_point_mgr_add(request):
    if request.method == 'POST':
        add_name = request.POST.get('add_name', None)
        add_sqltext = request.POST.get('add_sqltext', None)
        add_desc = request.POST.get('add_desc', None)
        add_limit = request.POST.get('add_limit', None)
        add_status = request.POST.get('add_status', None)
        add_remake = request.POST.get('add_remake', None)
        add_point_type = request.POST.get('add_point_type', None)
        cron_models.oratk_cron_point_info.objects.create(name=add_name, desc=add_desc, sqltext=add_sqltext,
                                                         limit=add_limit, status=add_status,
                                                         remake=add_remake, point_type=add_point_type)
        return redirect("/oratk_app/cron_point_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/cron_point_add.html')
        else:
            return redirect('/basic_app/login.html')


def cron_point_mgr_edit(request):
    if request.method == 'POST':
        v_id = request.POST.get('v_id', None)
        add_name = request.POST.get('add_name', None)
        add_desc = request.POST.get('add_desc', None)
        add_sqltext = request.POST.get('add_sqltext', None)
        add_limit = request.POST.get('add_limit', None)
        add_status = request.POST.get('add_status', None)
        add_remake = request.POST.get('add_remake', None)
        add_point_type = request.POST.get('add_point_type', None)
        cron_models.oratk_cron_point_info.objects.filter(id=v_id).update(name=add_name, desc=add_desc,
                                                                         sqltext=add_sqltext,
                                                                         limit=add_limit, status=add_status,
                                                                         remake=add_remake, point_type=add_point_type)
        return redirect("/oratk_app/cron_point_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            edit_result = cron_models.oratk_cron_point_info.objects.get(id=nid)
            return render(request, 'oratk_app/cron_point_edit.html', {'cron_point_info_result': edit_result})
        else:
            return redirect('/basic_app/login.html')


def cron_point_mgr_delete(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            point_info = cron_models.oratk_cron_point_info.objects.filter(id=nid).values('name').first()['name']
            try:
                task_point_info = \
                    cron_models.oratk_cron_task.objects.filter(task_point=point_info).values('task_point').first()[
                        'task_point']
                if task_point_info:
                    data = {'result': '不能删除该监控项目', 'msg': '该监控项目已被使用'}
                    return JsonResponse(data, json_dumps_params={'ensure_ascii': False})
                else:
                    # print(task_point_info)
                    cron_models.oratk_cron_point_info.objects.filter(id=nid).delete()
                    return redirect("/oratk_app/cron_point_mgr")
            except:
                cron_models.oratk_cron_point_info.objects.filter(id=nid).delete()
                return redirect("/oratk_app/cron_point_mgr")
        else:
            return redirect('/basic_app/login.html')


def cron_point_mgr_lock(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            cron_models.oratk_cron_point_info.objects.filter(id=nid).update(status='locked')
            return redirect("/oratk_app/cron_point_mgr")
        else:
            return redirect('/basic_app/login.html')


def cron_point_mgr_more(request):
    if request.method == 'POST':
        return redirect("/oratk_app/cron_point_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            more_result = cron_models.oratk_cron_point_info.objects.get(id=nid)
            return render(request, 'oratk_app/cron_point_more.html', {'more_result': more_result})
        else:
            return redirect('/basic_app/login.html')


#################以下 计划任务页-计划任务管理 #################
def cron_task_mgr(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            ##当用户搜索时：
            if user_search != 'all':
                cron_task_result = cron_models.oratk_cron_task.objects.filter(Q(task_info__contains=user_search)
                                                                              | Q(task_point__contains=user_search)
                                                                              | Q(task_time__contains=user_search)
                                                                              | Q(limit_status__contains=user_search)
                                                                              | Q(limit__contains=user_search)
                                                                              | Q(sms_status__contains=user_search)
                                                                              | Q(remake__contains=user_search)).values(
                    "id", "name", "name__description", "task_time",
                    "task_point", "task_info", "limit_status",
                    "limit", "limit_unit", "sms_status", 'name__date_changed',
                    "name__enabled", "remake", "instance", "sms_contact", "email_contact", "crtime", "chtime",
                    "task_type")
                v_paginator(cron_task_result, currentpage_num, line_count_set)
            # 当用户没有搜索时：
            else:
                user_search = ''
                cron_task_result = cron_models.oratk_cron_task.objects.all().values("id", "name", "name__description",
                                                                                    "task_time",
                                                                                    "task_point", "task_info",
                                                                                    "limit_status",
                                                                                    "limit", "limit_unit", "sms_status",
                                                                                    'name__date_changed',
                                                                                    "name__enabled", "remake",
                                                                                    "instance", "sms_contact",
                                                                                    "email_contact", "crtime", "chtime",
                                                                                    "task_type")
                # print(cron_task_result)
                v_paginator(cron_task_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/cron_task_mgr.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_add_sql(request):
    if request.method == 'POST':
        curr_time = time.strftime('%Y年%m月%d日 %H:%M:%S', time.localtime(time.time()))
        kwargs_dict = {}
        add_name = request.POST.get('add_name', None)
        add_desc = request.POST.get('add_desc', None)
        add_task_info = request.POST.get('add_tash_info', None)
        add_task_time = request.POST.get('add_task_time', None)
        add_task_point = request.POST.get('add_task_point', None)
        add_limit_status = request.POST.get('add_limit_status', None)
        add_limit = request.POST.get('add_limit', 'None')
        add_limit_unit = request.POST.get('add_limit_unit', '')
        add_sms_status = request.POST.get('add_sms_status', None)
        add_sms_conent = request.POST.get('add_sms_conent', None)
        # add_sms_contact  = request.POST.get('add_sms_contact', None)
        add_enable = request.POST.get('add_enable', None)
        add_remake = request.POST.get('add_remake', None)
        add_ins = request.POST.getlist('add_ins', None)
        add_sms_contact = request.POST.getlist('add_sms_contact', None)
        add_email_contact = request.POST.getlist('add_email_contact', None)
        add_task_type = request.POST.getlist('add_task_type', None)
        # 生成beat_crontabschedule的参数列表
        # arg_sqltext = cron_models.oratk_cron_point_info.objects.filter(name=add_task_point).first()
        # args_list.append(arg_sqltext.sqltext)
        # args_list.append(add_limit)
        # args_list.append(add_task_point)
        if add_enable == '是':
            add_enable = 1
        else:
            add_enable = 0
        if add_limit_status == '是':
            add_limit_status = 1
        else:
            add_limit_status = 0
        if add_sms_status == '是':
            add_sms_status = 1
        else:
            add_sms_status = 0
        ##kwargs字典
        kwargs_dict['task_name'] = add_name
        kwargs_dict['point_name'] = add_task_point
        kwargs_dict['limit_status'] = add_limit_status
        kwargs_dict['limit'] = add_limit
        kwargs_dict['limit_unit'] = add_limit_unit
        kwargs_dict['sms_status'] = add_sms_status
        kwargs_dict['sms_conent'] = add_sms_conent
        # kwargs_dict['sms_contact'] = add_sms_contact
        kwargs_dict['ins'] = add_ins
        kwargs_dict['sms_contact'] = add_sms_contact
        kwargs_dict['email_contact'] = add_email_contact
        task_kwargs = json.dumps(kwargs_dict)
        ##获取beat_crontabschedule中的id
        task_time = cron_models.oratk_cron_time_info.objects.filter(name=add_task_time).first()
        beat_models.PeriodicTask.objects.create(name=add_name, description=add_desc, task=add_task_info,
                                                kwargs=task_kwargs, crontab_id=task_time.type_id.id, enabled=add_enable)
        cron_models.oratk_cron_task.objects.create(name_id=add_name, task_info=add_task_info, task_point=add_task_point,
                                                   task_time=add_task_time,
                                                   limit=add_limit, remake=add_remake, limit_status=add_limit_status,
                                                   sms_status=add_sms_status, sms_conent=add_sms_conent,
                                                   sms_contact=add_sms_contact, email_contact=add_email_contact,
                                                   instance=add_ins, chtime=curr_time, task_type=add_task_type,
                                                   limit_unit=add_limit_unit)

        return redirect("/oratk_app/cron_task_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            ins_info = basic_models.instance_info.objects.exclude(status='locked')
            user_info = basic_models.user_info.objects.exclude(status='locked')
            task_info = cron_models.oratk_cron_task_info.objects.filter(type='sql类')
            task_time = cron_models.oratk_cron_time_info.objects.all()
            task_point = cron_models.oratk_cron_point_info.objects.filter(point_type='sql类')
            return render(request, 'oratk_app/cron_task_add.html',
                          {'task_info': task_info, 'task_time': task_time, 'task_point': task_point,
                           'ins_info': ins_info, 'user_info': user_info})
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_add_host(request):
    if request.method == 'POST':
        curr_time = time.strftime('%Y年%m月%d日 %H:%M:%S', time.localtime(time.time()))
        kwargs_dict = {}
        add_name = request.POST.get('add_name', None)
        add_desc = request.POST.get('add_desc', None)
        add_task_info = request.POST.get('add_tash_info', None)
        add_task_time = request.POST.get('add_task_time', None)
        add_task_point = request.POST.get('add_task_point', None)
        add_limit_status = request.POST.get('add_limit_status', '是')
        add_limit = request.POST.get('add_limit', '0')
        add_limit_unit = request.POST.get('add_limit_unit', '')
        add_sms_status = request.POST.get('add_sms_status', None)
        add_sms_conent = request.POST.get('add_sms_conent', None)
        add_enable = request.POST.get('add_enable', None)
        add_remake = request.POST.get('add_remake', None)
        add_ins = request.POST.getlist('add_ins', None)
        add_sms_contact = request.POST.getlist('add_sms_contact', None)
        add_email_contact = request.POST.getlist('add_email_contact', None)
        add_task_type = request.POST.get('add_task_type', None)
        print(add_ins)
        if add_enable == '是':
            add_enable = 1
        else:
            add_enable = 0
        if add_limit_status == '是':
            add_limit_status = 1
        else:
            add_limit_status = 0
        if add_sms_status == '是':
            add_sms_status = 1
        else:
            add_sms_status = 0
        ##kwargs字典
        kwargs_dict['task_name'] = add_name
        kwargs_dict['point_name'] = add_task_point
        kwargs_dict['limit_status'] = add_limit_status
        kwargs_dict['limit'] = add_limit
        kwargs_dict['limit_unit'] = add_limit_unit
        kwargs_dict['sms_status'] = add_sms_status
        kwargs_dict['sms_conent'] = add_sms_conent
        # kwargs_dict['sms_contact'] = add_sms_contact
        kwargs_dict['ins'] = add_ins
        kwargs_dict['sms_contact'] = add_sms_contact
        kwargs_dict['email_contact'] = add_email_contact
        task_kwargs = json.dumps(kwargs_dict)
        ##获取beat_crontabschedule中的id
        task_time = cron_models.oratk_cron_time_info.objects.filter(name=add_task_time).first()
        beat_models.PeriodicTask.objects.create(name=add_name, description=add_desc, task=add_task_info,
                                                kwargs=task_kwargs, crontab_id=task_time.type_id.id, enabled=add_enable)
        cron_models.oratk_cron_task.objects.create(name_id=add_name, task_info=add_task_info, task_point=add_task_point,
                                                   task_time=add_task_time,
                                                   limit=add_limit, remake=add_remake, limit_status=add_limit_status,
                                                   sms_status=add_sms_status, sms_conent=add_sms_conent,
                                                   sms_contact=add_sms_contact, email_contact=add_email_contact,
                                                   instance=add_ins, chtime=curr_time, task_type=add_task_type,
                                                   limit_unit=add_limit_unit)

        return redirect("/oratk_app/cron_task_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            ins_info = basic_models.instance_info.objects.exclude(status='locked')
            user_info = basic_models.user_info.objects.exclude(status='locked')
            host_info = basic_models.host_info.objects.exclude(status='locked')
            task_info = cron_models.oratk_cron_task_info.objects.filter(type='host类')
            task_time = cron_models.oratk_cron_time_info.objects.all()
            task_point = cron_models.oratk_cron_point_info.objects.filter(point_type='host类')
            return render(request, 'oratk_app/cron_task_add_host.html',
                          {'task_info': task_info, 'task_time': task_time, 'task_point': task_point,
                           'ins_info': ins_info, 'user_info': user_info, 'host_info': host_info})
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_edit(request):
    if request.method == 'POST':
        curr_time = time.strftime('%Y年%m月%d日 %H:%M:%S', time.localtime(time.time()))
        kwargs_dict = {}
        add_id = request.POST.get('add_nid', None)
        add_name = request.POST.get('add_name', None)
        add_desc = request.POST.get('add_desc', None)
        add_tash_info = request.POST.get('add_tash_info', None)
        add_task_time = request.POST.get('add_task_time', None)
        add_task_point = request.POST.get('add_task_point', None)
        add_limit_status = request.POST.get('add_limit_status', None)
        add_limit = request.POST.get('add_limit', None)
        add_limit_unit = request.POST.get('add_limit_unit', '')
        add_sms_status = request.POST.get('add_sms_status', None)
        add_sms_conent = request.POST.get('add_sms_conent', None)
        add_remake = request.POST.get('add_remake', None)
        add_ins = request.POST.getlist('add_ins', None)
        add_sms_contact = request.POST.getlist('add_sms_contact', None)
        add_email_contact = request.POST.getlist('add_email_contact', None)
        add_task_type = request.POST.get('add_task_type', None)
        # 生成beat_crontabschedule的参数列表
        arg_sqltext = cron_models.oratk_cron_point_info.objects.filter(name=add_task_point).first()
        ##kwargs字典
        kwargs_dict['task_name'] = add_name
        kwargs_dict['point_name'] = add_task_point
        kwargs_dict['limit_status'] = add_limit_status
        kwargs_dict['limit'] = add_limit
        kwargs_dict['limit_unit'] = add_limit_unit
        kwargs_dict['sms_status'] = add_sms_status
        kwargs_dict['sms_conent'] = add_sms_conent
        kwargs_dict['sms_contact'] = add_sms_contact
        kwargs_dict['email_contact'] = add_email_contact
        kwargs_dict['ins'] = add_ins
        task_kwargs = json.dumps(kwargs_dict)
        ##获取beat_crontabschedule中的id
        task_time = cron_models.oratk_cron_time_info.objects.filter(name=add_task_time).first()
        PeriodicTask_id = cron_models.oratk_cron_task.objects.filter(id=add_id).first()
        beat_models.PeriodicTask.objects.filter(id=PeriodicTask_id.name.id).update(name=add_name, description=add_desc,
                                                                                   task=add_tash_info,
                                                                                   kwargs=task_kwargs,
                                                                                   crontab_id=task_time.type_id.id)
        task_enable = beat_models.PeriodicTask.objects.get(id=PeriodicTask_id.name.id)
        task_enable.save()
        cron_models.oratk_cron_task.objects.filter(id=add_id).update(name_id=add_name, task_info=add_tash_info,
                                                                     task_point=add_task_point, task_time=add_task_time,
                                                                     limit=add_limit, remake=add_remake,
                                                                     limit_status=add_limit_status,
                                                                     sms_status=add_sms_status,
                                                                     sms_conent=add_sms_conent,
                                                                     sms_contact=add_sms_contact,
                                                                     email_contact=add_email_contact, instance=add_ins,
                                                                     chtime=curr_time,
                                                                     task_type=add_task_type, limit_unit=add_limit_unit)

        return redirect("/oratk_app/cron_task_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            nid_task_info = cron_models.oratk_cron_task.objects.filter(id=nid).values('task_info').first()
            # 生成beat_crontabschedule的参数列表
            host_info = basic_models.host_info.objects.exclude(status='locked')
            user_info = basic_models.user_info.objects.exclude(status='locked')
            ins_info = basic_models.instance_info.objects.exclude(status='locked')
            task_info = cron_models.oratk_cron_task_info.objects.all()
            task_time = cron_models.oratk_cron_time_info.objects.all()
            task_point = cron_models.oratk_cron_point_info.objects.all()
            cron_task_result = cron_models.oratk_cron_task.objects.filter(id=nid).values("id", "name",
                                                                                         "name__description",
                                                                                         "task_time", "task_point",
                                                                                         "task_info", "limit_status",
                                                                                         "limit", "limit_unit",
                                                                                         "sms_status", "sms_conent",
                                                                                         "sms_contact", "email_contact",
                                                                                         "name__enabled", "remake",
                                                                                         "instance",
                                                                                         "task_type").first()
            print(nid_task_info['task_info'])
            if nid_task_info['task_info'] == 'cron.tasks.task_kwargs':
                return render(request, 'oratk_app/cron_task_edit.html', {'cron_task_result': cron_task_result,
                                                                         'task_info': task_info, 'task_time': task_time,
                                                                         'task_point': task_point, 'ins_info': ins_info,
                                                                         'user_info': user_info})
            else:
                return render(request, 'oratk_app/cron_task_edit_host.html', {'cron_task_result': cron_task_result,
                                                                              'task_info': task_info,
                                                                              'task_time': task_time,
                                                                              'task_point': task_point,
                                                                              'ins_info': ins_info,
                                                                              'user_info': user_info,
                                                                              'host_info': host_info})
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_delete(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            task_name = cron_models.oratk_cron_task.objects.filter(id=nid).first()
            beat_models.PeriodicTask.objects.filter(name=task_name.name_id).delete()
            return redirect("/oratk_app/cron_task_mgr")
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_lock(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            task_name = cron_models.oratk_cron_task.objects.filter(id=nid).first()
            beat_models.PeriodicTask.objects.filter(name=task_name.name_id).update(enabled=0)
            task_enable = beat_models.PeriodicTask.objects.get(name=task_name.name_id)
            task_enable.enable = 0
            task_enable.save()
            return redirect("/oratk_app/cron_task_mgr")
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_enable(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            task_name = cron_models.oratk_cron_task.objects.filter(id=nid).first()
            beat_models.PeriodicTask.objects.filter(name=task_name.name_id).update(enabled=1)
            task_enable = beat_models.PeriodicTask.objects.filter(name=task_name.name_id).first()
            task_enable.enable = 1
            print(task_enable.id, task_enable.enable)
            task_enable.save()
            return redirect("/oratk_app/cron_task_mgr")
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_more(request):
    if request.method == 'POST':
        return redirect("/oratk_app/cron_point_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            more_result = cron_models.oratk_cron_point_info.objects.get(id=nid)
            return render(request, 'oratk_app/cron_point_more.html', {'more_result': more_result})
        else:
            return redirect('/basic_app/login.html')


###计划任务-定义时间
def cron_time_mgr(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            ##当用户搜索时：
            if user_search != 'all':
                cron_point_info_result = cron_models.oratk_cron_time_info.objects.filter(Q(name__contains=user_search)
                                                                                         | Q(
                    type__contains=user_search)).values("id", "name", "type", "type_id__id", "type_id__minute"
                                                        , "type_id__hour", "type_id__day_of_week"
                                                        , "type_id__day_of_month", "type_id__month_of_year",
                                                        "type_id__timezone")
                v_paginator(cron_point_info_result, currentpage_num, line_count_set)
            # 当用户没有搜索时：
            else:
                user_search = ''
                # cron_point_info_result = beat_models.CrontabSchedule.objects.all().order_by('-id')
                cron_point_info_result = cron_models.oratk_cron_time_info.objects.all().order_by('id').values("id",
                                                                                                              "name",
                                                                                                              "type",
                                                                                                              "type_id__id",
                                                                                                              "type_id__minute"
                                                                                                              ,
                                                                                                              "type_id__hour",
                                                                                                              "type_id__day_of_week"
                                                                                                              ,
                                                                                                              "type_id__day_of_month",
                                                                                                              "type_id__month_of_year",
                                                                                                              "type_id__timezone")
                v_paginator(cron_point_info_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/cron_time_mgr.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')


def cron_time_mgr_add(request):
    if request.method == 'POST':
        add_name = request.POST.get('add_name', None)
        add_minute = request.POST.get('add_minute', None)
        add_hour = request.POST.get('add_hour', None)
        add_day_of_week = request.POST.get('add_day_of_week', None)
        add_day_of_month = request.POST.get('add_day_of_month', None)
        add_month_of_year = request.POST.get('add_month_of_year', None)
        add_timezone = request.POST.get('add_timezone', None)
        add_type = request.POST.get('add_type', None)
        beat_models.CrontabSchedule.objects.create(minute=add_minute, hour=add_hour, day_of_week=add_day_of_week,
                                                   day_of_month=add_day_of_month,
                                                   month_of_year=add_month_of_year, timezone=add_timezone)
        CrontabSchedule_id = beat_models.CrontabSchedule.objects.all().order_by('-id').first()
        cron_models.oratk_cron_time_info.objects.create(name=add_name, type=add_type, type_id_id=CrontabSchedule_id.id)

        return redirect("/oratk_app/cron_time_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/cron_time_add.html')
        else:
            return redirect('/basic_app/login.html')


def cron_time_mgr_delete(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            time_info = cron_models.oratk_cron_time_info.objects.filter(type_id=nid).values('name').first()['name']
            try:
                task_time_info = \
                    cron_models.oratk_cron_task.objects.filter(task_time=time_info).values('task_time').first()[
                        'task_time']
                if task_time_info:
                    data = {'result': '不能删除该监控时间', 'msg': '该监控时间已被使用'}
                    return JsonResponse(data, json_dumps_params={'ensure_ascii': False})
                else:
                    # print(task_point_info)
                    beat_models.CrontabSchedule.objects.filter(id=nid).delete()
                    return redirect("/oratk_app/cron_time_mgr")
            except:
                beat_models.CrontabSchedule.objects.filter(id=nid).delete()
                return redirect("/oratk_app/cron_time_mgr")
        else:
            return redirect('/basic_app/login.html')






###后台进程管理
def cron_process_mgr(request):
    if request.method == 'GET':
        if request.session.get('is_login', None):
            request.session.set_expiry(0)


            check_redis_command = 'ps -ef | grep redis | grep -v grep'
            check_celery_beat_command = 'ps -ef | grep celery | grep beat | grep -v grep'
            check_celery_worker_command = 'ps -ef | grep celery | grep worker | grep -v grep'


            redis_dic_stats = {'id':1,'name':'redis','run_status':'unknow','run_style_str':'cursor:pointer','stop_style_str':'cursor:pointer'}
            check_redis = os.popen(check_redis_command)
            if len(check_redis.readlines()) > 0:
                redis_dic_stats['run_status'] = 'running'
                redis_dic_stats['color'] = 'green'
                redis_dic_stats['run_style_str'] = 'pointer-events: none; opacity: 0.2'
            else:
                redis_dic_stats['run_status'] = 'stop'
                redis_dic_stats['color'] = 'red'
                redis_dic_stats['stop_style_str'] = 'pointer-events: none; opacity: 0.2'

            celery_beat_dic_stats = {'id':2,'name': 'celery_beat', 'run_status': 'unknow','run_style_str':'cursor:pointer','stop_style_str':'cursor:pointer'}
            check_celery_beat = os.popen(check_celery_beat_command)
            if len(check_celery_beat.readlines()) > 0:
                celery_beat_dic_stats['run_status'] = 'running'
                celery_beat_dic_stats['color'] = 'green'
                celery_beat_dic_stats['run_style_str'] = 'pointer-events: none; opacity: 0.2'
            else:
                celery_beat_dic_stats['run_status'] = 'stop'
                celery_beat_dic_stats['color'] = 'red'
                celery_beat_dic_stats['stop_style_str'] = 'pointer-events: none; opacity: 0.2'

            celery_worker_dic_stats = {'id':3,'name': 'celery_worker', 'run_status': 'unknow','run_style_str':'cursor:pointer','stop_style_str':'cursor:pointer'}
            check_celery_worker = os.popen(check_celery_worker_command)
            if len(check_celery_worker.readlines()) > 0:
                celery_worker_dic_stats['run_status'] = 'running'
                celery_worker_dic_stats['color'] = 'green'
                celery_worker_dic_stats['run_style_str'] = 'pointer-events: none; opacity: 0.2'
            else:
                celery_worker_dic_stats['run_status'] = 'stop'
                celery_worker_dic_stats['color'] = 'red'
                celery_worker_dic_stats['stop_style_str'] = 'pointer-events: none; opacity: 0.2'

            return_list = [redis_dic_stats,celery_beat_dic_stats,celery_worker_dic_stats]
            #print(return_list)

            #return_list = [{'name':'redis','run_status':'running','option':1}]
            return render(request, 'oratk_app/cron_process_mgr.html',{'return_list':return_list})

        else:
            return redirect('/basic_app/login.html')
    if request.method == 'POST':
        pass

##后台进程开启或关闭
def cron_process_change(request):
    #1-redis
    #2-celery-beat
    #3-celery-worker
    if request.method == 'GET':
        if request.session.get('is_login', None):
            request.session.set_expiry(0)

            id = request.GET.get('nid')
            method = request.GET.get('method')

            #print(id,method)

            start_redis_command = 'redis-server &'
            start_celery_beat_command = 'source ~/.bash_profile && workon py_oratk_3.8.7&&cd /Users/liwk/py/oratk_web&&celery -A py_oratk_3 beat -l info --scheduler django_celery_beat.schedulers:DatabaseScheduler &'
            #getuser = 'whoami'
            start_celery_worker_command = 'source ~/.bash_profile && workon py_oratk_3.8.7&&cd /Users/liwk/py/oratk_web&&celery worker -A py_oratk_3 -l info &'


            stop_redis_command = 'redis-cli shutdown'
            stop_celery_beat_command = "ps -ef | grep celery| grep beat | awk '{print $2}'  | xargs kill -9"
            stop_celery_worker_command = "ps -ef | grep celery| grep worker | awk '{print $2}'  | xargs kill -9"
            if id == '1':
                if method == 'start':
                    print('redis start')
                    os.system(start_redis_command)
                    #print(a.readlines())
                if method == 'stop':
                    print('redis stop')
                    os.popen(stop_redis_command)

            if id == '2':
                if method == 'start':
                    print('celery beat start')
                    a = os.system(start_celery_beat_command)
                    #print(a.readlines())
                if method == 'stop':
                    print('celery beat stop')
                    os.popen(stop_celery_beat_command)

            if id == '3':
                if method == 'start':
                    print('celery worker  start')
                    os.system(start_celery_worker_command)
                if method == 'stop':
                    print('celery worker stop')
                    os.popen(stop_celery_worker_command)

            return redirect("/oratk_app/cron_process_mgr")

        else:
            return redirect('/basic_app/login.html')
    if request.method == 'POST':
        pass


#import cast
from django.db.models.functions import Cast
from django.db.models import DateTimeField
###计划任务执行情况
def cron_mon_exec_info(request):
    if request.method == 'GET':
        if request.session.get('is_login', None):
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            #cron_task_exec_info = cron_models.oratk_cron_exec_info.objects.all().order_by('-id')
            ##extra 把crtime的值精确到秒(去掉小数点后面的值)
            cron_task_exec_info = cron_models.oratk_cron_exec_info.objects.all().extra(select={'add_time':'DATE_FORMAT(crtime, "%%Y-%%m-%%d %%H:%%i:%%s")'}).order_by('-add_time','task_name')

            v_paginator(cron_task_exec_info, currentpage_num, line_count_set)
            return render(request, 'oratk_app/cron_task_exec_info.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')
    if request.method == 'POST':
        pass


###短信发送记录
def cron_mon_sms_send(request):
    if request.method == 'GET':
        if request.session.get('is_login', None):
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            cron_sms_exec_info = cron_models.oratk_cron_sms_send.objects.all().order_by('-id')
            v_paginator(cron_sms_exec_info, currentpage_num, line_count_set)
            return render(request, 'oratk_app/cron_sms_exec_info.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num,
                           'paginator_num_pages_list': paginator_num_pages_list
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')
    if request.method == 'POST':
        pass

